AWS Redshift -向现有表添加标识列

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

在AWS Redshift中，当试图使用该命令向现有表添加标识列时。

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

错误:ALTER TABLE ADD COLUMN不支持具有类型标识的列

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?

这显然意味着这是不允许红移的。我需要放下来重新创建桌子吗?或者有一些变通的解决方案来完成红移?

3 个解决方案

#1

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:

文档:不能使用ALTER TABLE ADD COLUMN命令修改以下表和列属性:
    UNIQUE

    PRIMARY KEY

    REFERENCES (foreign key)

    IDENTITY

#2

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

虽然您不能直接完成它，但是您可以通过几个步骤来完成它。如果现有表t1定义为:

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:

首先，创建一个具有与t1相同的列的新表，但是添加了您想要添加的标识列:

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:

然后，将t1的所有行插入t2中，填充标识列以外的所有列:

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;

#3

-1

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.

需要注意的是，这种方法只适用于将标识列添加到现有数据。当添加新行时，它不会自动递增。

#1