"how to create multiple sequences in one table?" Code Answer

sequence does not guarantee there are no gaps. for example, one transaction might generate a new number and then abort (due to a bug or a power failure or whatever...). the next transaction would then blindly get the next number, not the one that was "lost".

it would be best if your client application did not depend on "no gaps" assumption in the firs place. you could, however, minimize gaps like this:

select max(receipt_number) from receipts where customer_id = :ci
insert into receipts(customer_id, receipt_number) values (:ci, aboveresult+1), or just insert 1 if step 1 returned null.
if step 2 returned a pk violation^*, retry from the beginning.

^* because a concurrent transaction has gone through the same process and committed.

as long as rows are just added and not deleted, this should prevent any gaps, even in a concurrent environment.

btw, you can "condense" steps 1 and 2 like this:

insert into receipts (customer_id, receipt_number)
select :ci, coalesce(max(receipt_number), 0) + 1
from receipts
where customer_id = :ci;

[sql fiddle]

the index underneath the pk {customer_id, receipt_number} should ensure that the select part of this query is satisfied efficiently.

By joris on February 9 2022

Answers related to “how to create multiple sequences in one table?”

Only authorized users can answer the Search term. Please sign in first, or register a free account.

Browse Other Code Languages