将标识列添加到 Snowflake 中的现有表? [英] Add identity column to existing table in Snowflake?

问题描述

我有一张桌子MY_TABLE"；在我想添加标识列的 Snowflake 中.我试过了

I have a table "MY_TABLE" in Snowflake that I would like to add an identity column to. I tried

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'.

这在雪花中是不可能的吗?

Is this just not possible in snowflake?

为了解决这个限制，我尝试创建一个临时版本的表格

To try to get around this limitation, I tried to create a temp version of the table

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 查询中，所以我想知道我是否只是做错了这一切.有没有其他人遇到过类似的问题?

Which sort of makes sense, since I am not passing the primary key. At this point it seems like I may have to manually enter the list of x (which is a very high number) of column names into the sql query, so I am wondering if I am just doing this all wrong. Has anyone else run into a similar issue?

推荐答案

你能试试这个吗

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;

这篇关于将标识列添加到 Snowflake 中的现有表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！