How can I copy an IDENTITY field?

Question

I’d like to update some parameters for a table, such as the dist and sort key. In order to do so, I’ve renamed the old version of the table, and recreated the table with the new parameters (these can not be changed once a table has been created).

I need to preserve the id field from the old table, which is an IDENTITY field. If I try the following query however, I get an error:

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

You can't INSERT data setting the IDENTITY columns, but you can load data from S3 using COPY command.

First you will need to create a dump of source table with UNLOAD.

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

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