在Snowflake中向现有表添加身份列？

Question

我在Snowflake中有一个名为“MY_TABLE”的表，我想添加一个标识列。我尝试过：

ALTER TABLE "MY_TABLE" 
    ADD COLUMN primary_key int IDENTITY(1,1);

但这会返回

SQL compilation error: Cannot add column 'PRIMARY_KEY' with non-constant default to non-empty table 'MY_TABLE'.

在Snowflake中是否无法做到这一点？

为了尝试规避这个限制，我尝试创建了表的临时版本。

CREATE OR REPLACE TABLE "MY_TABLE_TEMP" LIKE "MY_TABLE"
ALTER TABLE "MY_TABLE_TEMP" ADD COLUMN primary_key int IDENTITY(1,1)

INSERT INTO "MY_TABLE_TEMP"
    SELECT * FROM "MY_TABLE";

但现在我遇到了错误

SQL compilation error: Insert value list does not match column list expecting <x+1> but got <x>

这有点有道理，因为我没有传递主键。此时，似乎我必须手动将x（一个非常大的数字）列名称列表输入到SQL查询中，所以我想知道我是否做错了。还有其他人遇到过类似的问题吗？

- Maile Cupo

---

Snowflake似乎不喜欢在现有的表中添加新的自增列。考虑到您的表似乎是空的，您可以直接修改创建语句。 - Tim Biegeleisen

Answer 1

你能试试这个吗

CREATE TABLE TEST_TABLE_TEMP LIKE TEST_TABLE;
ALTER TABLE TEST_TABLE_TEMP ADD COLUMN primary_key int IDENTITY(1,1);

create or replace sequence seq_01 start = 1 increment = 1;

INSERT INTO TEST_TABLE_TEMP 
SELECT *,seq_01.NEXTVAL FROM TEST_TABLE;

SELECT * FROM TEST_TABLE_TEMP;

- Rajib Deb

---

不是OP，但我遇到了同样的问题，而且ALTER TABLE语句无法通过OP的原始消息。 - Neuromante

实际上，Snowflake不应该启用ALTER TABLE解决方案来为非空表填充IDENTITY列，这是有原因的。答案在SNOWFLAKE的架构中。想想需要触及多少个微型分区以及这将对计算成本意味着什么？ - Rajib Deb

经进一步探索，我认为存在一个问题。这似乎使用了不同的序列来标识列，并种子化该列的初始值。下一行插入到表中时将重新从1开始。但是，我可以通过执行IDENTITY(r,1)来解决这个问题，其中r是表中行数。 - Maile Cupo

不错，2023年了仍然能够正常工作！ - Pouya Ataei

Answer 2

可以使用自己的SEQUENCE来为每行创建唯一的ID，而不是使用IDENTITY。

使用序列修复问题中的示例：

CREATE OR REPLACE SEQUENCE seq1;
CREATE OR REPLACE TABLE "MY_TABLE_TEMP" LIKE "MY_TABLE";

ALTER TABLE "MY_TABLE_TEMP" 
ADD COLUMN primary_key int DEFAULT seq1.nextval;


INSERT INTO "MY_TABLE_TEMP"
SELECT *, seq1.nextval 
FROM "MY_TABLE";

（在发布此答案后，我注意到它与Rajib的答案非常相似）

- Felipe Hoffa

---

不是楼主，但我处于类似的情况（使用序列而不是标识EDIT：同时也使用标识），当尝试执行你在上面写的ALTER TABLE时，我也遇到了与楼主相同的错误。 - Neuromante

Answer 3

您不需要创建序列。您只需在插入中指定列：

CREATE TABLE TEST_TABLE_TEMP LIKE TEST_TABLE;
ALTER TABLE TEST_TABLE_TEMP ADD COLUMN primary_key int IDENTITY(1,1);

INSERT INTO TEST_TABLE_TEMP (col1,col2,...) 
SELECT col1,col2,... FROM TEST_TABLE;

如果您想在除了最后以外的其他位置添加主列，您需要为表创建DDL。然后编辑它：添加您的主列并更改表名。插入将会生效。

- Andrzej

Answer 4

创建带有标识列的新表。将数据复制到新表中。删除原始表。重命名新表。

- Comey

Answer 5

我有一个猜想，你是否尝试在插入语句中包含目标列名。我假设如果你不指定它们，它会期望在选择语句中刚刚添加的额外列。

- Mike Gohl