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 1

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)