Ajouter une colonne d'identité à une table existante dans Snowflake ?

Question

J'ai une table "MY_TABLE" dans Snowflake à laquelle je voudrais ajouter une colonne d'identité. J'ai essayé

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

Mais ce retour

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

Est-ce que ce n'est pas possible dans Snowflake ?

Pour essayer de contourner cette limitation, j'ai essayé de créer une version temporaire de la 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";

Mais maintenant je reçois l'erreur

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

Ce qui est logique, puisque je ne passe pas la clé primaire. À ce stade, il semble que je doive entrer manuellement la liste des x (ce qui est un nombre très élevé) noms de colonnes dans la requête sql, et je me demande donc si je ne m'y prends pas mal. Quelqu'un d'autre a-t-il rencontré un problème similaire ?

Answer 1

Pouvez-vous essayer ceci

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

Au lieu d'utiliser IDENTITY vous pouvez utiliser votre propre SEQUENCE pour créer un identifiant unique pour chaque ligne.

Fixer l'exemple de la question avec une séquence :

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

(après avoir posté cette réponse, j'ai remarqué qu'elle est très similaire à celle de Rajib)

Answer 3

C'est juste une intuition, mais avez-vous essayé d'inclure le nom de la colonne cible dans l'instruction d'insertion ? Je suppose que si vous ne les spécifiez pas, le système s'attend à la colonne supplémentaire que vous venez d'ajouter dans l'instruction de sélection.

Answer 4

Il n'est pas nécessaire de créer une séquence. Vous pouvez simplement spécifier les colonnes dans l'insertion :

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;

Si vous voulez ajouter votre colonne primaire à un autre endroit que le dernier, vous devez créer une DDL pour votre table. Ensuite, modifiez-la : ajoutez votre colonne primaire et changez le nom de la table. L'insertion fonctionnera.