CODEWITHSUNDEEP
postgresql
Dark Templar
Dark Templar

Reputation: 1295

Serial type column value getting non-sequential values

I have a serial column created as follows:

CREATE TABLE public.table_name (
    "_id" serial4 NOT NULL,

The assumption I had, is that this column will have only sequential values, i.e. 1, 2, ..., n, where n- is the total number of rows in table table_name. However, this is not the case:

SELECT max(_id), count(*) FROM public.table_name;

yields:

959132  111933

I know that the assumption I had is most probably an erroneous one, but can you please show me the correct way to achieve what I am after, and preferably also how to update the already non-sequential values to make them sequential.

Postgres version:

PostgreSQL 13.2 (Ubuntu 13.2-1.pgdg18.04+1)

Upvotes: 2

Views: 884

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246033

That is normal. Sequences get gaps if

  • you call nextval() but don't use the value

  • you call nextval() and use the value in an INSERT, but the transaction fails and is rolled back

  • the database crashes

  • you created the sequence with CACHE greater than 1 and close a database session

People often want a “gap-less sequence”, but most of the time that is not really necessary. You can get that if you use a special counter table that you UPDATE whenever you need the next value, but that will serialize all transactions that need a value.

There is no way to get a gap-less sequence without a severe performance impact.

You can find more details in my article on that topic.

Upvotes: 2

Related Questions