Finding contiguous primary keys

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

-- 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;

Now the solution

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