How to change table schema after created in Redshift?

You can create a new table with

CREATE TABLE schema1.tableName( LIKE schema2.tableName INCLUDING DEFAULTS ) ;

and then copy the contents of table from one schema to another using the INSERT INTO statement

followed by DROP TABLE to delete the table.

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.

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.