Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

create table t (
  number int
)

CREATE TABLE

insert into t (number)
values (1), (2), (4)

INSERT 0 3

with recursive all_numbers AS (
  select min(number) AS number
       , max(number) AS last_number
    from t
   union all
  select number + 1
       , last_number
    from all_numbers
   where number < last_number
)
select number
  from all_numbers

number

1

2

3

4

SELECT 4

with recursive all_numbers AS (
  select min(number) AS number
       , max(number) AS last_number
    from t
   union all
  select number + 1
       , last_number
    from all_numbers
   where number < last_number
)
select number
  from all_numbers
 where number not in (select number from t);

number

3

SELECT 1