0

I want to keep a logical auto-increment in the ID field.

It is defined as SERIAL, but as soon as I delete a row (ex:15) and later insert a new one, the ID becomes 16 (so, it jumps from 14 to 16). What's the best way to handle that?

user1249791
  • 1,241
  • 4
  • 14
  • 33
  • 5
    What is the problem? There is nothing wrong with having gaps in your sequence values. –  May 07 '14 at 12:12
  • See here: http://stackoverflow.com/questions/6224274 and here: http://stackoverflow.com/questions/16908432 and here: http://stackoverflow.com/questions/19004453 and here: http://stackoverflow.com/questions/2298841 –  May 07 '14 at 12:14
  • 1
    In fact, you better get used to it - you should **expect** gaps, there's too many reasons you'll get them. The exact value of autoincrement ids is unimportant. In fact, you can write your entire application without needing to touch the db-side ids. – Clockwork-Muse May 07 '14 at 12:16
  • If you have an actual requirement for a gap-free sequence (e.g. invoices) then this is probably the best question: http://stackoverflow.com/questions/19004453/postgresql-generate-sequence-with-no-gap – IMSoP May 07 '14 at 12:18

1 Answers1

0

The best way to handle that? That's easy. Ignore this non-problem.

The serial data type is guaranteed to put in an auto incrementing value. It does not guarantee much else. So, gaps are allowed, and they do appear -- for instance, as a result of deletes. This is a good thing. The primary purpose of serial columns to create primary keys. A primary key uniquely identifies each row and the integer data type is convenient for joining tables together. No reason to change a value along the way.

If you want an auto-incremented sequential value, then get what you want on output using row_number():

select t.*,
       row_number() over (order by id) as MySequentialValueWithNoGaps
from table t
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I know that in functionality there is no problem. But set a SELECT WHERE ID=220 when you only have 6 rows (deletes where applied before) sounds weird... – user1249791 May 07 '14 at 12:27
  • 1
    Who cares if it sounds weird? Databases use SQL, not sound, anyway... – Frank Heikens May 07 '14 at 13:04
  • 1
    @user1249791: if that looks "weird" then you have not understood the concept of a primary key. –  May 07 '14 at 13:07
  • well, in my opinion SQL queries should have a kind of human logic. ID=220 sounds like there are 220 records. Even if it works, for me sounds weird. – user1249791 May 07 '14 at 14:04
  • There can be 10 million rows, who knows? And what about values in other tables? You don't know and you don't care. It's only a number that is used to identify a unique row. Or do you really want to update every row in your database when you delete just one single record in one table? Everyone who's still using the old id, is also out of luck: They don't know the new number for this record so they can't find the data anymore. – Frank Heikens May 07 '14 at 14:32
  • This ID should be used as a primary key, referenced by others, so that a ON UPDATE CASCADE constraint is added on referenced tables and we don-t lose the link between both. – user1249791 May 07 '14 at 14:37