Select all integers that are not already in table

问题:

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.

回答1:

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:

• Select rows which are not present in other table

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;

回答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)