Postgresql sequences
Posted
by
Dylan
on Stack Overflow
See other posts from Stack Overflow
or by Dylan
Published on 2011-01-12T02:47:10Z
Indexed on
2011/01/12
2:53 UTC
Read the original article
Hit count: 211
When I delete all records from a Postgresql table and then try to reset the sequence to start a new record with number 1 when it is inserted, i get different results :
SELECT setval('tblname_id_seq', (SELECT COALESCE(MAX(id),1) FROM tblname));
This sets the current value of the sequence to 1, but the NEXT record (actually the first because there are no records yet) gets number 2!
And I can't set it to 0, because the minimum value in the sequence is 1!
When I use :
ALTER SEQUENCE tblname_id_seq RESTART WITH 1;
the first record that is inserted actually gets number 1 ! But the above code doesn't accept a SELECT as a value instead of 1.
I wish to reset the sequence to number 1 when there are no records, and the first record then should start with 1. But when there ARE already records in the table, I want to reset the sequence so that the next record that is inserted will get {highest}+1
Does anyone have a clear solution for this?
© Stack Overflow or respective owner