There are a lot of questions and answers about either finding the first 'gap' or the beginning and ending of a gap.
I do not want that. I want to return the first n unused values.
Eg: Suppose I have a table slots:
slot_id | slot
--------------
1 | 2
2 | 4
3 | 5
4 | 7
The first 5 unused values are 0,1,3,6,8
How would I get that?
Probably the simplest method is to generate all the values and choose the ones that don't exist:
select gs.slot
from generate_series(0, 8) gs(slot)
where not exists (select 1
from slots s
where s.slot = gs.slot
)
order by gs.slot
limit 5;
EDIT:
If you want to guarantee that you get at least 5 back, you can use:
select gs.slot
from (select generate_series(0, max(slot) + 5) as slot
from slots
) gs
where not exists (select 1
from slots s
where s.slot = gs.slot
)
order by gs.slot
limit 5;
