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?

score 52 · Answer 1 · edited Nov 10 '20 at 12:29

52

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.

edited Nov 10 '20 at 12:29

Eric Hansander

944
2
11
20

answered Feb 21 '18 at 21:04

Joe Harris

13,671
4
47
54

2

Because of the problems with views, I recomend creating views "WITH NO SCHEMA BINDING". – dsz Jul 12 '18 at 01:12
3

One thing to note with this approach is that while it is really fast, it also ends up emptying the source table at the end of the operation. Learnt this the hard way, and I wish I had done the all-important step of RTFM on the ALTER TABLE ... APPEND operation, after running it twice and ending up with empty source and target tables. Answer is valid though. – CodingInCircles Feb 28 '20 at 03:24
As someone who has NOT been burned by this, let me say that it's inexcusable that Amazon just lets you APPEND from an empty table, with no warning, confirmation, or anything. Not even anything in the INFO afterwards, telling you you just lost data. Simply thoughtless. – DHW Nov 14 '21 at 18:05

score 10 · Accepted Answer · answered Mar 27 '14 at 12:31

10

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.

answered Mar 27 '14 at 12:31

Iñaki Soria

869
7
15

2

Note, if you create the new table with `create newschema.table1 (like table1);` sortkey, distkey, and constraints will be copied over, but primary key and foreign keys will not (they are informational only anyway). You could also copy the table over with `select * into newschema.table1 from table1;` without having to create new table first, though this will not copy over not null constraints, primary key, and foreign keys. – Dmitrii I. Apr 08 '15 at 15:52
@DmitriiI. Does this query also copy grants associated with old table? – himanshu219 Jan 05 '19 at 08:46

score 5 · Answer 3 · edited Feb 20 '18 at 19:24

5

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.

edited Feb 20 '18 at 19:24

Ville

4,088
2
37
38

answered Sep 08 '15 at 04:38

Ganesh Todkar

507
5
12

score -3 · Answer 4 · answered Apr 03 '18 at 07:36

This is how i do it.

-- Drop if you have already one backup

DROP TABLE IF EXISTS TABLE_NAME_BKP CASCADE;

-- Create two back up, one to work and will be deleted at the end, and one more is real backup

SELECT * INTO TABLE_NAME_BKP FROM TABLE_NAME;
SELECT * INTO TABLE_NAME_4_WORK FROM TABLE_NAME;

--We can do also do the below ALTER, but this holds primary key constraint name, hence you cant create new table with same constraints names

ALTER TABLE TABLE_NAME RENAME TO TABLE_NAME_4_WORK;

-- Ensure you have copied

SELECT COUNT(*) FROM TABLE_NAME;
SELECT COUNT(*) FROM TABLE_NAME_4_WORK;

-- Create the new table schema

    DROP TABLE IF EXISTS TABLE_NAME CASCADE;

    CREATE TABLE TABLE_NAME     (
       ID           varchar(36)     NOT NULL,
       OLD_COLUMN   varchar(36),
       NEW COLUMN_1 varchar(36)
    )
    compound sortkey (ID, OLD_COLUMN, NEW COLUMN_1);

    ALTER TABLE TABLE_NAME
    ADD CONSTRAINT PK__TAB_NAME__ID
    PRIMARY KEY (id);

-- copy data from old to new

INSERT INTO TABLE_NAME (
     id,    
     OLD_COLUMN)
 (SELECT     
     id,        
     OLD_COLUMN FROM TABLE_NAME_4_WORK)

-- Drop the work table TABLE_NAME_4_WORK

 DROP TABLE TABLE_NAME_4_WORK;

-- COMPARE BKP AND NEW TABLE ROWS, AND KEEP BKP TABLE FOR SOMETIME.

 SELECT COUNT(*) FROM TABLE_NAME_BKP;

 SELECT COUNT(*) FROM TABLE_NAME;

How to change table schema after created in Redshift?

4 Answers4

Linked