CODEWITHSUNDEEP
amazon-redshift
Dean
Dean

Reputation: 9108

How can I copy an IDENTITY field?

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?

Upvotes: 8

Views: 24050

Answers (3)

Tomasz Tybulewicz
Tomasz Tybulewicz

Reputation: 8647

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.

Upvotes: 22

naviram
naviram

Reputation: 1475

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

If the target table contains columns that don't exist in the source table, include FILLTARGET. The command fills the extra columns in the source table with either the default column value or IDENTITY value, if one was defined, or NULL.

It moves the columns from one table to another, extremely quickly; took me 4s for 1GB table in dc1.large node.

Appends rows to a target table by moving data from an existing source table.
...
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 simpler than UNLOAD + COPY with EXPLICIT_IDS.

Upvotes: 1

marcusb
marcusb

Reputation: 630

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

Upvotes: 7

Related Questions