Add identity column to existing table in Snowflake?

Question

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);

But this returns

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";

But now I get the error

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

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?

Answer 1

Can you try this

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;

Answer 2

Instead of using IDENTITY, you could use your own SEQUENCE to create a unique id for each row.

Fixing the example in the question with a sequence:

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";

(after posting this answer, I noticed it's very similar to Rajib's)

Answer 3

Just a hunch, but have you tried including the target_col_name in the insert statement. I'm assuming that if you don't specify them, it is expecting the additional column you just added in the select statement.

Answer 4

You do not need to create sequence. You can just specify columns in the insert:

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;

If you want to add your primary column in other place than last you need to create DDL for your table. Than edit it: add your primary column and change the table name. The insert will work.