Postgresql sequences

Question

When I delete all records from the 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 due to the lack of records) 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 does not take the SELECT value as a value instead of 1.

I want to reset the sequence with number 1 when there are no records, and the first record should start with 1. But when ARE is already writing to the table, I want to reset the sequence so that the next inserted record gets {maximum} +1

Does anyone have a clear solution for this?

Answer 1

Use the setval three-argument setval to set the is_called flag to false so that it returns the current sequence value for the next nextval call, rather than generating a new one right away.

http://www.postgresql.org/docs/current/interactive/functions-sequence.html

Also note that you need to use COALESCE(MAX(id),0)+1 , otherwise the first value in the sequence will be MAX(id) , which, as you know, already exists. (thanks Stephen Denne)

Answer 2

See http://www.postgresql.org/docs/current/static/functions-sequence.html at the bottom of the page.

In particular, at least in Postgresql 9.0 you can find and set the sequence value. You can use the form with three arguments setval() to set either the current value of the sequence or the next value of the sequence (which allows you to set sequence 1 the next time you search for values).