I recently had the need to figure out which ranges of my keys were contiguously numbered. The related exercise is finding gaps in data as well.
Reasons might be because you need to determine what data did not get copied or what records got deleted. There are lots of ways of accomplishing this, but this is the
first that came to mind. This approach uses window aggregates lead
function and common table expressions, so requires PostgreSQL 8.4+
Create dummy data with gaps to test with
CREATE TABLE s(n integer);
INSERT INTO s(n)
SELECT n
FROM generate_series(1,1000) As n
WHERE n % 200 != 0;
Now the solution
WITH
n1 AS (SELECT n AS start_n
FROM s
EXCEPT
SELECT n + 1 AS start_n
FROM s),
n2 AS (SELECT n1.start_n
, lead(start_n) OVER (ORDER BY start_n) As next_set_n
FROM n1
GROUP BY n1.start_n)
SELECT start_n, MAX(COALESCE(s.n,start_n)) As end_n
FROM n2 LEFT JOIN s ON( s.n >= n2.start_n AND (s.n < n2.next_set_n or n2.next_set_n IS NULL))
GROUP BY start_n, next_set_n
ORDER BY start_n;
The result of the above query looks like:
start_n | end_n
----------+-------
1 | 199
201 | 399
401 | 599
601 | 799
801 | 999
Tracked: Jul 01, 20:52