Below are the steps to move table from one schema to another in Redshift database.
Step 1: Create table in new schema Step 2: Copy data from old table to new table Step 3: Drop (or rename) old table (optional step)
Here are the sql statements to implement the above steps. I’m moving table1 from schema1 to schema2 in this example.
You may either want to drop the old table or preserve the old table with the same name or a different name. Implement step 3 accordingly.
Create table schema2.table1 (like schema1.table1 including defaults); Insert into schema2.table2 (select * from schema1.table1); (optionally) Drop table schema1.table1; or alter table schema1.table1 rename to schema1.bkp_table1;