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+
-- our test table -- this creates gaps after 199 records CREATE TABLE s(n integer); INSERT INTO s(n) SELECT n FROM generate_series(1,1000) As n WHERE n % 200 != 0;
WITH -- get start ranges (numbers that don't have a matching next + 1) n1 AS (SELECT n AS start_n FROM s EXCEPT SELECT n + 1 AS start_n FROM s), -- for each start range find the next start range 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) -- determine end range for each start -- end range is the last number that is before start of next range 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