AWS Redshift - Add IDENTITY column to existing table

Question

In AWS Redshift, when attempting to add an IDENTITY column to an existing table using the command

ALTER TABLE table_name ADD COLUMN id bigint IDENTITY(1,1);

I get the following error

ERROR: ALTER TABLE ADD COLUMN does not support columns with type IDENTITY

Which obviously implies that this simply isn't allowed in Redshift. Will I need to drop and recreate the table? Or is there some workaround solution to get this done in Redshift?

Answer 1

While you can't do it directly, you can accomplish this in a few steps. If we have existing table t1 defined as:

CREATE TABLE t1 (
c1 vachar(MAX),
c2 int
);

First, create a new table that has the same columns as t1, but with the addition of the identity column that you want to add:

CREATE TABLE t2 (
id bigint IDENTITY(1,1),
c1 varchar(MAX),
c2 int
);

Then, insert all of the rows of t1 into t2, filling every column other than the identity column:

INSERT INTO t2 (c1, c2)
(SELECT * FROM t1);

Now we can drop the original table:

DROP TABLE t1

And finally rename the new table to original table:

ALTER TABLE t2
RENAME TO t1;

Answer 2

You must add IDENTITY column during table declaration. And you can't add it later.

Docs: You cannot use an ALTER TABLE ADD COLUMN command to modify the following table and column attributes:

    UNIQUE

    PRIMARY KEY

    REFERENCES (foreign key)

    IDENTITY