Date: Mon, 10 Jun 2024 15:57:26 -0700 (PDT)
From: Rich Shepard <rshepard@appl-ecosys.com>
Reply-To: Rich Shepard <rshepard@appl-ecosys.com>
To: pgsql-general@lists.postgresql.org
Subject: Gaps in PK sequence numbers
Message-ID: <35fd10ab-5f7f-7a57-9cc5-c03d81476d13@appl-ecosys.com>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=US-ASCII
Archived-At: <https://www.postgresql.org/message-id/35fd10ab-5f7f-7a57-9cc5-c03d81476d13%40appl-ecosys.com>
Precedence: bulk

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