Find first available value that doesn’t exist

I want to create table for book chapters where pk will be book_id and chapter_internal_number. I’m not sure how find next free chapter_internal_number value for new chapter insert (chapter can be deleted and it’s chapter_internal_number value should be reused).

How to find first chapter_internal_number avaiable value for book? Avaiable value is next value that doesn’t exist in ASC order.

Table book_chapter:

|    pk   |           pk            |
| book_id | chapter_internal_number |
| 1       |          1              |
| 1       |          2              |
| 1       |          5              |
| 2       |          1              |
| 2       |          2              |
| 2       |          3              |

Expected:

• for book_id=1 is 3
• for book_id=2 is 4

Answer

Basically, you want the first gap in the chapter numbers for each book. I don’t think that you need generate_series() for this; you can just compare the current chapter to the next, using lead():

select book_id, min(chapter_internal_number) + 1
from (
    select bc.*, 
        lead(chapter_internal_number) over(partition by book_id order by chapter_internal_number) lead_chapter_internal_number
    from book_chapter bc
) bc
where lead_chapter_internal_number is distinct from chapter_internal_number + 1
group by book_id

This seems to be the most natural way to phrase your query, and I suspect that it should be more efficient that enumerating all possible values with generate_series() (I would be interested to know how both solutions comparatively perform against a large dataset).

We could also use distinct on rather than aggregation in the outer query:

select distinct on (book_id) book_id, chapter_internal_number + 1
from (
    select bc.*, 
        lead(chapter_internal_number) over(partition by book_id order by chapter_internal_number) lead_chapter_internal_number
    from book_chapter bc
) bc
where lead_chapter_internal_number is distinct from chapter_internal_number + 1
order by book_id, chapter_internal_number