How to get a list of numbers missing in a PostgreSQL database?

Question

PostgreSQL

The database contains the following data (for example):

1. id: 1 | name: "any_name"
   
2. id: 2 | name: "any_name"
   
3. id: 4 | name: "any_name"
   
4. id: 7 | name: "any_name"
   
5. id: 8 | name: "any_name"
   
6. id: 11 | name: "any_name"
   

How to get numbers not included in the output - 3,5,6,9,10 - numbers that are not in the database with one request?

Answer 1

Need a reference list of values ​​and a not exists subquery. The reference list can be made via the generate_series function

select n from generate_series(1,11) as n
where not exists (select from tablename where id = n)

Answer 2

IMHO, if such a question arose, then it is POSSIBLE that you are doing something wrong. Broadcasting business sense on IDb, the main purpose of which is to ensure uniqueness, is generally not very correct.
Nevertheless...
Perhaps there is a more beautiful solution, but it does not immediately occur to me (it will be different for each database).
General approach:
1. Create a table t2 with a large number of rows and one field num. In the loop, fill it with values ​​from 1 to n.
2. In the query, minus the source table from t2
SELECT t.num FROM t2
EXCEPT
SELECT t.id FROM base_table t
all.
PS If you are too lazy to make a table, and the number of records is small, then you can use
SELECT unnest (array [1, 2, 3, 4)