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)