Swapping Schemas
Jim_Knicely
- Select Field - Administrator
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
0