How to change table schema after created in Redshift?

Question

Postgre supports this operation as below:

ALTER TABLE name
    SET SCHEMA new_schema

The operation won't work in Redshift. Is there any way to do that?

I tried to update pg_class to set relnamespace(schema id) for the table, which needs superuser account and usecatupd is true in pg_shadow table. But I got permission denied error. The only account who can modify pg system table is rdsdb.

server=# select * from pg_user;
 usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil |            useconfig             
------------+----------+-------------+----------+-----------+----------+----------+----------------------------------
 rdsdb      |        1 | t           | t        | t         | ******** |          | 
 myuser     |      100 | t           | t        | f         | ******** |          | 

So really redshift gives no permission for that?

Answer 1

Quickest way to do this now is as follows:

CREATE TABLE my_new_schema.my_table (LIKE my_old_schema.my_table);
ALTER TABLE my_new_schema.my_table APPEND FROM my_old_schema.my_table;
DROP TABLE my_old_schema.my_table;

The data for my_old_schema.my_table is simply remapped to belong to my_new_schema.my_table in this case. Much faster than doing an INSERT INTO.

Important note: "After data is successfully appended to the target table, the source table is empty" (from AWS docs on ALTER TABLE APPEND), so be careful to run the ALTER statement only once!

Note that you may have to drop and recreate any views that depend on my_old_schema.my_table. UPDATE: If you do this regularly you should create your views using WITH NO SCHEMA BINDING and they will continue to point at the correct table without having to be recreated.

Answer 2

The best way to do that is to create a new table with the desired schema, and after that do an INSERT .... SELECT with the data from the old table.

Then drop your current table and rename the new one with ALTER TABLE.