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
4
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
3
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
4
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
3
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.
需要注意的是,这种方法只适用于将标识列添加到现有数据。当添加新行时,它不会自动递增。