Serial type column value getting non-sequential values

Question

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)

Your assumption is indeed incorrect. But what you are after is also incorrect. The only job of a generated primary key value is to be unique. It's completely irrelevant if that value is 1, 42, 3673628, -363 or something else. Gaps are expected and nothing to worry about. Just move on. — user330315
– user330315, Commented Nov 12, 2021 at 15:22
If a row is deleted from the middle of the table, should all later rows have their _id changed? — τεκ
– τεκ, Commented Nov 12, 2021 at 15:28
@τεκ there are no deletions in this table, only inserts and upserts. — Dark Templar
– Dark Templar, Commented Nov 12, 2021 at 15:33
When an upsert attempts to insert, it calls nextval() consuming a number from the sequence even if there is a conflict and the row is not inserted. — τεκ
– τεκ, Commented Nov 12, 2021 at 15:39
It is sequential it is just not gapless. As pointed that is expected as the sequence generator behind serial does not rollback when a transaction does, it just moves on. — Adrian Klaver
– Adrian Klaver, Commented Nov 12, 2021 at 16:09

Laurenz Albe · Accepted Answer · 2021-11-12 16:14:47Z

2

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.

answered Nov 12, 2021 at 16:14

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Serial type column value getting non-sequential values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related