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:
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