Search code examples
sqlpostgresqlsubquerysql-insert

getting lowest available column number for insert postgres

I have a application where I want clothing groups to be assigned to a rack number. The rack number should be as low as possible, and the groups are frequently replaced (recycled clothing). So how would I find the lowest possible rack_number on the following table?

clothing_groups table

[ id: sequal, rack_number: smallint ]

Currently we are retrieving an ascended list of all rack_numbers iterating through them to find the first gap, and using it for the insertion of the next clothing_groups

SELECT
    rack_number
FROM
    clothing_groups
ORDER BY
    rack_number ASC;

Which seems to be suboptimal at best, I'd rather either retrieve a single row or preferrably do a subquery within the insert finding the first available rack_number

INSERT INTO 
    clothing_groups (rack_number)
VALUES
   SELECT first_available_rack_number FROM clothing_groups WHERE ...

However I do not know if or how it is possible.

Any combination of rack_number rows is possible with gaps. That means it is possible for the sequence to be [1,2,3,4,5,6,8,12,14..., 231,432,500] and it changes dynamically based on the deletions and insertions. The only thing that is guaranteed is that there exists a "missing" number lower than 999.

Solution

  • One option uses a correlated subquery:

    INSERT INTO clothing_groups (rack_number)
SELECT MIN(rack_number) + 1
FROM clothing_groups cg
WHERE NOT EXISTS (SELECT 1 FROM clothing_groups cg1 WHERE cg1.rack_number = cg.rack_number + 1)

    You could also use window functions:

    INSERT INTO clothing_groups (rack_number)
SELECT MIN(rack_number) + 1
FROM (
    SELECT rack_number, LEAD(rack_number) OVER(ORDER BY rack_number) lead_rack_number
    FROM clothing_groups
) cg
WHERE lead_rack_number IS DISTINCT FROM rack_number + 1