Geek Answers Handbook

How to change table schema after creation in Redshift?

Postgre supports this operation as shown below:

ALTER TABLE name SET SCHEMA new_schema

In Redshift, the operation will not work. Is there any way to do this?

I tried updating pg_class to set the relnamespace (schema identifier) ​​for the table that needs the superuser account and usecatupd in the pg_shadow table. But I got permission to reject the error. The only account that can modify the 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, does the redshift really not give permission for this?

+10
source share
4 answers

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

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

+5
source

The fastest 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 simply reassigned so that in this case it belongs to my_new_schema.my_table . Much faster than doing an INSERT INTO .

Note that you may have to delete and recreate any views that depend on my_old_schema.my_table . UPDATE: If you do this regularly, you must create your views using WITH NO SCHEMA BINDING and they will continue to point to the correct table without the need for WITH NO SCHEMA BINDING creation.

+18
source

You can create a new table with

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

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

and then DROP TABLE to delete the table.

+4
source

This is how I do it.

- Delete if you already have one backup

 DROP TABLE IF EXISTS TABLE_NAME_BKP CASCADE;

- Create two backups, one for work and delete at the end, and one more - a real backup

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

--We can also execute the ALTER below, but it contains the primary key constraint name, therefore, you cannot create a new table with the same constraint names

 ALTER TABLE TABLE_NAME RENAME TO TABLE_NAME_4_WORK;

- Make sure you copy

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

- create a 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)

- Remove the desktop TABLE_NAME_4_WORK.

  DROP TABLE TABLE_NAME_4_WORK;

- COMPARE BKP AND NEW TABLES, AND SAVE BKP TABLE FOR SOMETIMES.

  SELECT COUNT(*) FROM TABLE_NAME_BKP; SELECT COUNT(*) FROM TABLE_NAME;
-1
source

More articles:


All Articles