1

I have id column in my table, which is:

  id     BIGSERIAL NOT NULL PRIMARY KEY, ...

after some rows where deleted the table gets inconsistent id's like 1, 10, 11, 12 ... i.e actual row count doesn't go with pg's serial id... I figured I should be somehow reinnumerating rows, is that right?

juk
  • 2,179
  • 4
  • 19
  • 25
  • Why would you want to renumber all your rows? – willglynn Dec 14 '12 at 18:06
  • hmm, because after 9 deletes I want that after 1 would go 2 instead of 10 – juk Dec 14 '12 at 18:25
  • 6
    No, PostgreSQL's `serial` and `bigserial` don't work like that nor would you want them to. Your `id`s are not row numbers nor should you think of them that way, they're just opaque values that are unique per-table and that's it. – mu is too short Dec 14 '12 at 18:36
  • http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-INT - Section 8.1.4 – Scott S Dec 14 '12 at 19:16
  • 4
    If the IDs are meaningless synthetic identifiers, you shouldn't need to renumber them. If they have meaning and need to be changed, then you shouldn't be using them as a sequence-generated primary key. – willglynn Dec 14 '12 at 19:25
  • ok i sticked it with row_number() over() – juk Dec 14 '12 at 19:27

1 Answers1

2

If you want gapless, sequential identifiers, don't use a SERIAL or BIGSERIAL (SEQUENCE). This is a FAQ. Sequences not only don't re-use deleted IDs, but they can also have gaps in them even if you don't delete anything because IDs are discarded when transactions roll back or after an unclean restart of the DB.

Search for "postgresql gapless sequence".

I wrote a bit aobut this here.

If you're using synthetic keys, you should only care about whether an ID is equal to another one. Don't try to compare them to say "how many IDs are between A and B" ; that doesn't make sense with SEQUENCE-generated IDs.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778