Find starting ids of ranges deleted from a SQL database

Question

I have a large database, with some gaps in the row identities or IDs (id INTEGER NOT NULL PRIMARY KEY, so typically ranging 1..x), indicating that some rows have been deleted at some time in the past. How can I query to get the IDs of the missing deleted rows? The rows tend to be deleted in contiguous ranges, and I just want some ID in each such range. E.g.

2 3 6 7

I just need 1, and 4 or 5 - and I could live with getting 8 (and even a false positive of 7 since I'm going to be reviewing it manually...) even you never really know how many, or if any, were deleted at the top end.

[This is a slightly simpler question than https://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table because I don't need the whole deleted ranges it is amenable to much simpler answers - you can find some workable answers there though]

Answer might depend on the database engine you are using. Please tag your question with correct database engine and version. The way to go here is to create a temporary table with all ids between 1 and max(id) of what you currently have and then use ANTI JOIN to list only those, that aren't present within the master table. However, this solution will be omitting the IDs that were recently deleted (have higher ID number than current max). — Kamil Gosciminski, Jan 26 '22 at 13:54
@wildplasser Because 4 and 5 are a contiguous range, I don't need both 4 and 5. — Tim Lovell-Smith, Jan 27 '22 at 16:16

score 0 · Answer 1 · answered Jan 27 '22 at 16:32

Using exists() this is simple comme bonjour :

CREATE TABLE with_holes
        ( id integer primary key
        );
INSERT INTO with_holes(id) VALUES (2), (3), (6), (7);


SELECT id-1 AS just_before
FROM with_holes wh
WHERE NOT EXISTS ( select * FROM with_holes nx WHERE nx.id = wh.id -1 )
        ;

Result:

CREATE TABLE
INSERT 0 4

 just_before 
-------------
           1
           5
(2 rows)

Find starting ids of ranges deleted from a SQL database

1 Answers1