1

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?

ldd
  • 99
  • 1
  • 2
  • 9

1 Answers1

2

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • this is awesome and it works great for my use case since I know the upper limit of how many slots I can have (about 100). I'm still going to wait to see other answers that do not assume an upper limit. Thanks! – ldd Feb 21 '21 at 03:41