postgresql insert into from select

Question

I have two tables table1 and test_table1 which have the same schema.

Both tables have rows/data and pk id's starting from 1.

I would like to do:

insert into test_table1 select * from table1;

but this fails due to the pk values from table1 existing in test_table1.

Way around it would be to specify columns and leave the pk column out, but for some reason thats not working either:

e.g. NOTE - no pk columns in query below

insert into test_table1 (col1, col2,..., coln) select col1,col2,...,coln from table1;

returns

ERROR: duplicate key value violates unique constraint "test_table1_pkey" DETAIL: Key (id)=(1) already exists.

I know this works in MySql, is this just due to Postgresql? Anyway around it?

EDIT:

Both tables have primary keys and sequence set.

Since it wasn't clear - tables don't have the same data. I would just like to add rows from table1 to test_table1.

For answers telling me to exclude primary key from the query - I did as I said before.

Which version? If you are using 9.5 INSERT .. ON CONFLICT may work for you. — Nick
– Nick, Commented Jul 20, 2016 at 19:06
You have defined your table to have a primary key and you are inserting a value to it that exists already. It worked in MySQL because your MySQL table didn't have a unique primary key defined on it. Give us the table schema for your test tables (including constraints) for a full answer. (should clarify...do the rows already exist in test_table1 and you're trying to insert again, or are you looking to update the values in test_table1 to be the same as table1?) — Twelfth
– Twelfth, Commented Jul 20, 2016 at 19:13
List all the columns except the primary key. A new primary key will be assigned, however. — Gordon Linoff
– Gordon Linoff, Commented Jul 20, 2016 at 19:19
I've edited the question - pk column was never in the query but it does exist on both tables with sequence. — Jerry Smith
– Jerry Smith, Commented Jul 21, 2016 at 13:35
If you have excluded primary key column from insert query and pk constraint error still occures there will be only one reason. — Taleh Ibrahimli
– Taleh Ibrahimli, Commented Jul 21, 2016 at 19:03

Taleh Ibrahimli · Accepted Answer · 2016-07-20 20:49:20Z

4

Just remove pk column from columns of query

insert into test_table1 (col2,..., coln) select col2,...,coln from table1;

If it still fails maybe you have not sequence on pk columns. Create sequence on already existing pk column

create sequence test_table1_seq;
ALTER TABLE test_table1 
    ALTER COLUMN col1 SET DEFAULT nextval('test_table1_seq'::regclass);

And update sequence value to current

SELECT setval('test_table1_seq', (SELECT MAX(col1) FROM test_table1));

answered Jul 20, 2016 at 20:49

Taleh Ibrahimli

7604 gold badges13 silver badges32 bronze badges

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

1 Comment

Jerry Smith Over a year ago

Hi Taleh, tnx for the reply, I've edited the question - pk column was never in the query but it does exist on both tables with sequence.

Community · Accepted Answer · 2020-06-20 09:12:55Z

This post helped me solve my problem, not sure what went wrong:

How to fix PostgreSQL error "duplicate key violates unique constraint"

If you get this message when trying to insert data into a PostgreSQL database:

ERROR: duplicate key violates unique constraint

That likely means that the primary key sequence in the table you're working with has somehow become out of sync, likely because of a mass import process (or something along those lines). Call it a "bug by design", but it seems that you have to manually reset the a primary key index after restoring from a dump file. At any rate, to see if your values are out of sync, run these two commands:
SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval('the_primary_key_sequence');
If the first value is higher than the second value, your sequence is out of sync. Back up your PG database (just in case), then run thisL
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);
That will set the sequence to the next available value that's higher than any existing primary key in the sequence.

Rahul · Accepted Answer · 2016-07-20 19:04:50Z

0

You rather would want to do a UPDATE JOIN like

UPDATE test_table1 AS v 
SET col1 = s.col1,
col2 = s.col2,
col3 = s.col3,
.....
colN = s.colN
FROM table1 AS s
WHERE v.id = s.id;

answered Jul 20, 2016 at 19:04

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

Richard Albright · Accepted Answer · 2016-07-20 23:37:25Z

0

what you want to do is an upsert.

with upsert as (
    update test_table1 tt
    set col1 = t.col1,
        col2 = t.col2,
        col3 = t.col3
    from table1 t
    where t.id = tt.id 
    returning *
)
insert into test_table1(id, col1, col2, col3)
select id, col1,col2,col3
from table1
where not exists (select * from upsert)

answered Jul 20, 2016 at 23:37

Richard Albright

3391 silver badge7 bronze badges

Collectives™ on Stack Overflow

postgresql insert into from select

4 Answers 4

1 Comment

How to fix PostgreSQL error "duplicate key violates unique constraint"

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

How to fix PostgreSQL error "duplicate key violates unique constraint"

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related