How to insert new record with sequence on postgres

Question

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.

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
– Juan-Carlos, Commented Feb 8, 2017 at 13:29
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
– Juan-Carlos, Commented Feb 8, 2017 at 13:32
Just omit the sequence :insert into new_table ( a, b, c) select a,b,c from old_table; — wildplasser
– wildplasser, Commented Feb 8, 2017 at 13:54

Vao Tsun · Accepted Answer · 2017-02-08 13:52:50Z

0

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.

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

answered Feb 8, 2017 at 13:52

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

2 Comments

You can combine the check the setting of the new value using select setval('my_sequence', (select max(id) from new_table))

should I add this to answer? I thought to put it as simple as possible