CODEWITHSUNDEEP
sqldatabasepostgresql
Jerry Smith
Jerry Smith

Reputation: 91

postgresql insert into from select

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.

Upvotes: 3

Views: 12585

Answers (4)

Jerry Smith
Jerry Smith

Reputation: 91

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.

Upvotes: 1

Richard Albright
Richard Albright

Reputation: 239

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)

Upvotes: 0

Taleh Ibrahimli
Taleh Ibrahimli

Reputation: 759

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

Upvotes: 4

Rahul
Rahul

Reputation: 77876

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;

Upvotes: 0

Related Questions