Swapping Schemas

Jim_Knicely

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 schema “schema1” with schema “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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERSCHEMA.htm