0

I have create one table with a sequence id(starting from 1), so when I try to save one itens if the save fails the db lost a value in sequence, so if I try to save again with the correct value he catch the 2 number and not the 1, can I fix this? to only get the next number when the insert works fine?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Fabio Ebner
  • 2,613
  • 16
  • 50
  • 77
  • Look into the `serial` data type: https://www.postgresql.org/docs/9.6/static/datatype-numeric.html. – Gordon Linoff Oct 31 '17 at 12:16
  • Please **[EDIT]** your question and add the `create table` statement and the `insert` statements you used. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Oct 31 '17 at 12:20
  • 1
    no, you can't. this is done to protect you. one sequence is nextvalled - no matter if transaction commits or fails, sequence is rolled forward – Vao Tsun Oct 31 '17 at 12:21
  • @GordonLinoff, Dont you think Serial will also allow gaps in the data? – Ankit Bajpai Oct 31 '17 at 12:27
  • This is definitely a duplicate, but I can't find a good target at the moment. There seem to be lots of questions that should be merged in this search: https://stackoverflow.com/search?tab=votes&q=postgres%20sequence%20gaps They will all tell you the same thing: this is expected behaviour, not a problem for 99.9% of use cases, and incredibly hard to do differently in a safe way. – IMSoP Oct 31 '17 at 12:50
  • 1
    This has quite a well-summarised question and answer, maybe the others should be closed against it: [PostgreSQL gapless sequences](https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences) – IMSoP Oct 31 '17 at 13:00

0 Answers0