Finding 'holes'

From: Magnus Hagander <mha(at)edu(dot)sollentuna(dot)se>
To: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Subject: Finding 'holes'
Date: 1998-05-18 14:52:29
Message-ID: 51F537775B63D0119D8D00805FBEB256CE495C@venture.edu.sollentuna.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi!

I need (well, not really need, but want to) a query that can give me the
"holes" in a sequence.
For example, I have a table which contains
id data
-- ----
1 xxx
2 yyy
4 zzz
6 ...

I would then like a query that can return the values 3 and 5, since
those are the ones not existing in the table.
I guess I could create a table which contains all the values 1..max(id)
and then do a
SELECT id FROM all_ids WHERE id NOT IN (SELECT id FROM datatable)

but I would really like to make it without the need to create such a
table (which will in time be very large and unnecessary).

Does anybody know of a way to do this?

//Magnus

Browse pgsql-sql by date

  From Date Subject
Next Message Jose' Soares Da Silva 1998-05-18 15:38:25 Re: [SQL] New User -- PG features
Previous Message Rachel Kay Street 1998-05-18 14:50:11 SQL Syntax: char to varchar type conversion(s)