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?

Yes, actually, it's one of the handful of differences in Redshift's implementation compared to Postgres. You can see it here http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html#identity-clause — MJ., May 21 '15 at 16:22
serial is not supported by Redshift: http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-datatypes.html — TinaW, Aug 04 '16 at 16:33

score 16 · Answer 1 · answered Sep 23 '16 at 18:02

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;

score 7 · Answer 2 · answered May 21 '15 at 17:02

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

score -1 · Answer 3 · answered Mar 08 '16 at 22:36

If all you want to do is add a unique column to an existing table, the following might be useful:

CREATE TABLE tableWithId AS 
(SELECT ROW_NUMBER() OVER () AS id, * FROM originalTable)

It is important to note that this workaround is only valid for adding identity column to existing data. It won't autoincrement when new rows are added.

AWS Redshift - Add IDENTITY column to existing table

3 Answers3

Linked