Insert from Select Postgresql with primary key constraint

Question

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?

Andreas · Accepted Answer · 2014-04-16 23:24:21Z

4

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

edited Apr 16, 2014 at 23:24

answered Apr 16, 2014 at 22:38

Andreas

5,1562 gold badges28 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeff S Over a year ago

I just tried this and it either doesn't work or I have something set up improperly. It's trying to use "Temp"."Column2" as the ID field that I want defaulted and throwing this error: ERROR: invalid input syntax for integer: "Test" In statement: INSERT INTO "A" SELECT 'Test';

Andreas Over a year ago

I think we need to specify the fields we're filling in. I'll edit

The Great Over a year ago

Hi, can you help me with this? Am trying to solve a similar problem stackoverflow.com/questions/59713505/…

Collectives™ on Stack Overflow

Insert from Select Postgresql with primary key constraint

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related