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

score 1 · Accepted Answer · answered Aug 02 '20 at 22:50

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)

answered Aug 02 '20 at 22:50

GMB

thanks for the answer and the advice! is there any performance benefit from a method over the other you presented? – Leonardo da Silva Aug 02 '20 at 22:56
1

@LeonardoSilva: you would need to assess performance against your real data. I like the first solution better because it is more concise. On the other one, the second solution would take advantage of an index on `chest(idx)`, if you have one. – GMB Aug 02 '20 at 23:03
this query fails to return 0 when it is the available index, how can I fix it? I made another question: https://stackoverflow.com/questions/63315637/get-first-available-index-in-the-table-including-0 – Leonardo da Silva Aug 08 '20 at 12:54

1 Answers1