I have an application that often does insertion into PostgreSQL database, for some tables I have id column with serial type with auto-generated sequences. Running

SELECT nextval(pg_get_serial_sequence('client', 'id'));

on each new session gives 1. What should I do to correct this, or I need to this manually in my app?

For last case, I think get total records and alter sequence, but this is 2 more queries to do, which I better skip.

closed as off-topic by Michael Green, Max Vernon, RolandoMySQLDBA, dezso, Andriy M Apr 1 '16 at 9:11

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Michael Green, Max Vernon, RolandoMySQLDBA, dezso, Andriy M
If this question can be reworded to fit the rules in the help center, please edit the question.


What you want is how sequences in PostgreSQL generally behave.

You must have some rogue process which is issuing a command such as:

alter sequence client_id_seq restart;

Or something equivalent to that. Figure out what is issuing that command, and make it stop.

If you can't figure out who is sabotaging your sequence, there are a variety of ways to figure it out. One is to crank up your log level by setting log_statement to 'ddl' or 'all', and look in the logs. Another is to make your table and sequence owned by a role which does not normally log into the database, and grant rights to the table and sequence to the normal log-in users. They will then get errors when they try to reset the sequence, because only the owner can do that. (This is a good practice anyway, to have objects owned by special roles which do not log in except when it is necessary to change the schema.)

  • Thanks! This is very helpful, I'll test my db with your suggestions – sigrlami Mar 31 '16 at 17:48
  • because only the owner can do that. And superusers, of course. And SECURITY DEFINER functions owned by either. – Erwin Brandstetter Mar 31 '16 at 23:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.