How to copy the IDENTITY field?

Question

I like to update some parameters for the table, such as the dist and sort key. To do this, Ive renamed the old version of the table and recreated the table with new parameters (they cannot be changed after creating the table).

I need to save the id field from an old table, which is an IDENTITY field. However, if I try to execute the following query, I get an error message:

insert into edw.my_table_new select * from edw.my_table_old; ERROR: cannot set an identity column to a value [SQL State=0A000] 

How can I keep the same id from the old table?

Answer 1

INSERT data cannot be set in IDENTITY columns, but you can load data from S3 using the COPY command .

First you need to dump the source table with UNLOAD .

Then simply use COPY with EXPLICIT_IDS , as described in Loading default column values :

If the IDENTITY column is included in the column list, the EXPLICIT_IDS parameter must also be specified in the COPY command or the COPY command will fail. Similarly, if the IDENTITY column is omitted from the list column and the EXPLICIT_IDS option, the COPY operation will fail.

Answer 2

You can explicitly specify columns and ignore the identifier column: insert into existing_table (col1, col2) select col1, col2 from another_table;

Answer 3

Use ALTER TABLE APPEND twice, the first time with IGNOREEXTRA and the second time with FILLTARGET.

If the destination table contains columns that are not in the source table, including FILLTARGET. The command fills in additional columns in the source table with the default column value or IDENTITY value, if one was defined, or NULL.

It moves columns from one table to another, very fast; I took 4s for 1 GB of the table in the dc1.large node.

Adds rows to the destination table, moving data from an existing table source.
...
ALTER TABLE APPEND is usually much faster than a similar CREATE TABLE AS or INSERT INTO operation because data is moved, not duplicated.

Faster and easier than UNLOAD + COPY with EXPLICIT_IDS.