Gaps in PK sequence numbers

From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Gaps in PK sequence numbers
Date: 2024-06-10 22:57:26
Message-ID: 35fd10ab-5f7f-7a57-9cc5-c03d81476d13@appl-ecosys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Over the years I've deleted rows from tables leaving gaps in the PK sequence
numbers. I've now learned that using nextval('sequencename') finds those
gaps and tells me that the value after the gap is already assigned.

I found a web page that explains how to find the gaps in a sequence, yet I
want to understand why nextval() doesn't begin with the max(FK)+1 value.

When I tried inserting new rows in the companies table psql told me that PK
value 2310 already existed. Selecting max(PK) returned 2341. When entering
multiple new rows is there a way to ignore gaps?

TIA,

Rich

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Munro 2024-06-10 22:59:28 Re: libpq v17 PQsocketPoll timeout is not granular enough
Previous Message Rich Shepard 2024-06-10 22:35:58 Re: Multiple tables row insertions from single psql input file [RESOLVED]