Swapping Schemas: Quick Tip

Renaming schemas is useful for swapping schemas without actually moving data. To facilitate the swap, enter a non-existent, temporary placeholder schema. But be careful with hardcoded schema names in SQL code like views! They won’t be swapped. Example: dbadmin=> create schema schema1; CREATE SCHEMA dbadmin=> create schema schema2; CREATE SCHEMA dbadmin=> create table schema1.test1 (c int); CREATE TABLE dbadmin=> create table schema2.test1 (c int); CREATE TABLE dbadmin=> create table schema2.test2 (c int); CREATE TABLE dbadmin=> INSERT INTO schema1.test1 SELECT 1; OUTPUT ——– 1 (1 row) dbadmin=> INSERT INTO schema2.test1 SELECT 2; OUTPUT ——– 1 (1 row) dbadmin=> COMMIT; COMMIT dbadmin=> CREATE VIEW schema1.test1_vw AS SELECT * FROM schema1.test1; CREATE VIEW dbadmin=> SELECT * FROM schema1.test1_vw; c — 1 (1 row) Now I will swap “schema1” with “schema2”: dbadmin=> ALTER schema schema1, schema2, temp_schema rename TO temp_schema, schema1, schema2; ALTER SCHEMA Checking the schema contents, we see that the tables moved: dbadmin=> \dt schema1.*; List of tables Schema | Name | Kind | Owner | Comment --------+-------+-------+---------+--------- schema1 | test1 | table | dbadmin | schema1 | test2 | table | dbadmin | (2 rows) dbadmin=> \dt schema2.*; List of tables Schema | Name | Kind | Owner | Comment ——–+——-+——-+———+——— schema2 | test1 | table | dbadmin | (1 row) But what about the view? dbadmin=> SELECT table_schema, view_definition dbadmin-> FROM views dbadmin-> WHERE table_name = 'test1_vw'; table_schema | view_definition -------------+----------------------------------- schema2 | SELECT test1.c FROM schema1.test1 (1 row) Note that the view did swap schemas, but its definition remained the same, so it is referencing the original schema! dbadmin=> SELECT test1.c FROM schema1.test1; c --- 2 (1 row) You’ll need to fix the view definition manually. dbadmin=> \! vsql -Atc "SELECT 'CREATE OR REPLACE VIEW ' || table_schema || '.' || table_name || ' AS ' || REPLACE(view_definition, 'schema1.', 'schema2.') || ';' FROM views WHERE table_nast1_vw';" | vsql CREATE VIEW dbadmin=> SELECT table_schema, view_definition dbadmin-> FROM views dbadmin-> WHERE table_name = ‘test1_vw’; table_schema | view_definition ————–+———————————– schema2 | SELECT test1.c FROM schema2.test1 (1 row) dbadmin=> SELECT * FROM schema2.test1_vw; c — 1 (1 row) That’s better! Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERSCHEMA.htm Have fun!