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.

Answer 1

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

Answer 2

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.