1

If in SQL Server we have a table with auto-increment, and 10 rows, and the last row has id = 10. When you delete the last row, the next you insert will get the id = 10 too, right?

But in Postgres, using bigserial as PK, when I delete the row with the max id, and insert a new row, it keeps incrementing more and more the pk number..

Is that right?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
jean
  • 11
  • 1
  • 2
  • Even if you don't delete from the table there can still be gaps in the numbering. The ID is just that - an ID. It doesn't tell you anything about the order rows were inserted or committed in, or much else really. – Craig Ringer Jun 04 '13 at 02:09

1 Answers1

5

That's right.

bigserial and serial are just notational convenience for creating a bigint / integer column with the default set to nextval() from a connected sequence.

The underlying SEQUENCE is never decreased, serial numbers are never reused (without manual intervention). Read the manual about sequences.

It needs to be that way for safe concurrent use.

In Postgres 10 or later consider IDENTITY columns instead. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228