query first available slot postgres

Question

I have a table called chest

chest_id integer NOT NULL
index integer NOT NULL

I can get the next index by querying

select max(index) + 1 from chest group by chest_id

In case there is some index in the order that is not filled, how to get it? for example:

chest_id | index
       0 |     0
       1 |     1
       2 |     2
       1 |     4

How would I query to return the first available index? in the example above it would be 3. But if it was filled also the next available would be 5

Answer 1

You can use window functions:

select idx + 1
from (select idx, lead(idx) over(order by idx) lead_idx from chest) t
where idx + 1 is distinct from lead_idx 

This gives you the first available idx in the table (either a gap, or the greatest value + 1).

Note that index is a language keyword, hence not a good choice for a column name. I renamed it to idx.

Another option is not exists:

select c.idx + 1
from chest c
where not exists (select 1 from chest c1 where c1.idx = c.idx + 1)