Postgresql sequences

Question

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?

araqnid · Accepted Answer · 2011-01-12 03:05:00Z

11

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

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

Also note you need to use COALESCE(MAX(id),0)+1, otherwise the first value from the sequence will be MAX(id), which you know to already exist. (thx Stephen Denne)

edited Jan 12, 2011 at 3:05

answered Jan 12, 2011 at 2:52

araqnid

135k25 gold badges163 silver badges138 bronze badges

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

3 Comments

Stephen Denne Over a year ago

And use COALESCE(MAX(id),0)+1

araqnid Over a year ago

Yes, or else the next id will collide with MAX(id).

Dylan Over a year ago

Great it works now, thanx.... I did look into that parameter, but it didn't work when I tried it. I must have done something wrong...

Andrew · Accepted Answer · 2011-01-12 02:52:46Z

2

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

Specifically, at least in Postgresql 9.0, you can find and set the value of a sequence. You can use the three-argument form of setval() to set either the current value of the sequence or the next value of the sequence (which would allow you to set the sequence to 1 on the next value retrieval).

answered Jan 12, 2011 at 2:52

Andrew

14.5k4 gold badges46 silver badges65 bronze badges

Collectives™ on Stack Overflow

Postgresql sequences

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related