The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Swapping Schemas

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

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.