How to insert new record with sequence on postgres

0
votes

I have a table 'new_table' with sequence id as primary key. how can I insert new records to it with data from other table. I tried:

insert into new_table ( 
(select (select NEXTVAL('my_sequence')),a,b,c from old_table)
);

but got

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

1
postgresql
First, check to see what you are actually getting back from the sequence object ( run this part : select NEXTVAL('my_sequence'). Your Sequence and your table my have gotten out of sequence resulting in a conflict. - Juan-Carlos
Second, isolate your sequence selection its own selection statement, putting it into a variable, then using that variable within the insert statement. It will be easier for debugging, and for future maintenance. - Juan-Carlos
TNX Maybe you can write an example ? - Gilo
Just omit the sequence :insert into new_table ( a, b, c) select a,b,c from old_table; - wildplasser

1 Answers

0
votes

  1. just list columns skipping the one with sequence, smth like:

    insert into new_table (a,b,c) select a,b,c from old_table;

If default value is set to get nextval, you don't have to do it manually.

  1. check if your sequence is not behind the max(id) value. smth like:

select max(id) from new_table

if it is, restart sequence with new cellar, smth like:

alter sequence my_sequence restart with 1999; or what your maximum is