How to find missing numbers?

I have a table containing a list of numbers. How do I find numbers that are missing from the table?

For instance:

numbers   id
 1         1
 5         2
 3         3
 6         4
 7         5
 8         6
 9         7
 20        8
 ....

How can I find missing numbers? Like 4 and there are no numbers between 9 and 20.

I have tried nothing, but want to know.

Assuming you want all missing integer numbers between the minimum and maximum existing id in your table - in a current Postgres 9.5 installation like you commented (or at least 9.3):

@Seb3W already suggested generate_series(). It's more efficient to retrieve min and max in a single query, though. It's also preferable to use set-returning-functions like generate_series() in the FROM list instead of the SELECT list (conforming to standard SQL and less error prone).

SELECT id
FROM  (SELECT min(id) AS a, max(id) AS z FROM numbers) x, generate_series(a, z) id
LEFT   JOIN numbers n1 USING (id)
WHERE  n1.id IS NULL;

Once you have the complete set of candidate numbers, use a run-of-the-mill technique to ...

• Select rows which are not present in other table

About the LATERAL join:

• What is the difference between LATERAL and a subquery in PostgreSQL?

If you don't care about standard SQL and want to squeeze out the last drop of performance (or in Postgres 9.2 or older without LATERAL joins) you can use generate_series() in the SELECT list, but still make it a single SELECT:

SELECT id
FROM  (SELECT generate_series(min(id), max(id)) FROM numbers) n(id)
LEFT   JOIN numbers n1 USING (id)
WHERE  n1.id IS NULL;

If you are after performance, you should have an index on numbers.id, of course:

CREATE INDEX numbers_id_idx ON numbers (id);

StackExchange Database Administrators Q#146144, answer score: 4