CODEWITHSUNDEEP
postgresqldatabase-schema
ebosi
ebosi

Reputation: 1467

PostgreSQL 11: How to "alter schema if exists rename to…"

Context
I am writing a (Python) script to automate the (re)creation from scratch of the schema foo on my PostgreSQL 11.2 database.

I want my script to handle both following cases:

Problem
I'm not very brave, and would prefer to keep a copy of this already existing schema, instead of simply DROP SCHEMA IF EXISTS foo;.

However, the command ALTER SCHEMA IF EXISTS foo RENAME TO foo_backup_2019-07-29T1317210; does not exists:

# ALTER SCHEMA IF EXISTS foo RENAME TO foo_backup_2019-07-29T1317210;
ERROR:  syntax error at or near "EXISTS"
LINE 1: ALTER SCHEMA IF EXISTS foo RENAME TO foo_backup_2019-07-29T1...

Question

How to rename foo at the condition such a schema already exists in my database?

Upvotes: 3

Views: 3994

Answers (2)

Richard Huxton
Richard Huxton

Reputation: 22952

Better than a one-use procedure just have a DO block.

https://www.postgresql.org/docs/current/sql-do.html

Upvotes: 0

Dave G
Dave G

Reputation: 11

You can create an SQL procedure that tries the ALTER SCHEMA command and traps the exception that occurs if the schema you are altering does not exist...

CREATE OR REPLACE PROCEDURE public.rename_schema_if_exists(from_schema name, to_schema name)
LANGUAGE plpgsql
AS $BODY$
DECLARE
  cmd text := format(E'ALTER SCHEMA %I RENAME TO %I;', from_schema, to_schema);
BEGIN
  EXECUTE cmd;
  EXCEPTION WHEN invalid_schema_name THEN RAISE NOTICE 'schema "%" does not exist', from_schema;
END;
$BODY$;

Use CALL to execute the procedure

CALL rename_schema_if_exists('schema_a','schema_b');

So, if the schema you are renaming doesn't exist, the underlying transaction is not aborted. It just logs a message to that effect and continues on.

Note that the procedure only traps the invalid_schema_name exception. Any other errors will not be trapped and so will cause the underlying transaction to abort. For example, if the to_schema already exists then a duplicate_schema exception is thrown, which our procedure doesn’t trap. If you don't want that error to abort the transaction then you can modify the procedure to trap that exception as well.

Upvotes: 1

Related Questions