CODEWITHSUNDEEP
sqlpostgresql
Jeff S
Jeff S

Reputation: 25

Insert from Select Postgresql with primary key constraint

I'm trying to copy data from one table into another with identical schemas in Postgresql. There is a primary key constraint on the tables, so running this fails due to a duplicate key issue:

INSERT INTO "A" SELECT * From "Temp";
SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "A_Id_pkey"

In MySQL, I can get around this by calling out each schema column and replacing the primary key with NULL.

INSERT INTO "A" SELECT NULL, "Temp"."Column2", "Temp"."Column3"... FROM "Temp";

However, this fails in Postgresql, despite the fact that my primary key's DEFAULT is set to nextval('a_id_seq'::regclass):

SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "Id" violates not-null constraint

Is there any syntax in Postgresql that will allow me to Select a "DEFAULT" placeholder for an upcoming INSERT statement?

Upvotes: 2

Views: 4101

Answers (1)

Andreas
Andreas

Reputation: 5113

If the ID is generated (ie default set to the next val of a sequence), just leave it out and copy the rest.

INSERT INTO "A" 
("Column2","Column3","Column4"...)
SELECT "Temp"."Column2", "Temp"."Column3"... FROM "Temp";

Upvotes: 4

Related Questions