1

I have some ids in a table, but there are gaps in between. I want to select these gaps.
For example, the integer numbers in my table are:

1
2
5
9
15

And I want to select:

3
4
6
7
8
10
11
12
13
14

My version of PostgreSQL is 9.1.1, so I cannot use int4range.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
thecoparyew
  • 715
  • 1
  • 9
  • 24

2 Answers2

5

Use generate_series() and LEFT JOIN to the table:

SELECT g.nr
FROM   generate_series(1,15) g(nr)
LEFT   JOIN tbl USING (nr)
WHERE  tbl.nr IS NULL;

Replace all occurrences of nr with your actual column name.
Or use one of the other basic techniques:

To determine the range dynamically:

SELECT g.nr
FROM  (SELECT generate_series(min(nr), max(nr)) AS nr FROM tbl) g
LEFT   JOIN tbl USING (nr)
WHERE  tbl.nr IS NULL;
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • You probably want to use `(select max(nr) from tbl)` rather than hardcoding the upper limit. –  Aug 22 '14 at 07:15
  • Thanks, generate_series() was missing :) – thecoparyew Aug 22 '14 at 07:20
  • I have problem with left join. I replaced all tbls with my table name and nr in second row with column I have. When I run query I get this error: ERROR: column "nr" specified in USING clause does not exist in right table – thecoparyew Aug 22 '14 at 07:54
  • @thecoparyew: Use the same name as your column name. Replace all instances of `nr` in my answer. – Erwin Brandstetter Aug 22 '14 at 08:07
  • @ErwinBrandstetter I just did that. But now query takes about 400s and still running. Table has about 2mio rows, and will just increase. So is there any chance to speed up this query? – thecoparyew Aug 22 '14 at 08:12
  • @thecoparyew: You did not mention millions of rows in the question - where all basic information should be. The query is as simple and fast as it gets. 400 seconds seems excessive. Writing a table should be a matter of a few seconds at most. You are not, by any chance, loading 2 Mio. rows into your client (which would be pretty useless). I am off to bed now, good luck – Erwin Brandstetter Aug 22 '14 at 08:23
2
with t (id) as (
values (1), (2), (5), (9), (15)
)
select * from generate_series((select min(id) from t), (select max(id) from t)) as g(id)
where g.id not in (select id from t)
alexius
  • 2,501
  • 20
  • 21