Finding 'holes'
Magnus Hagander <mha@edu.sollentuna.se>
From: Magnus Hagander <mha@edu.sollentuna.se>
To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Date: 1998-05-18T14:52:29Z
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