Re: Invoice increment

On 26/02/2020 10:27, Vik Fearing wrote:
> WITH
> u (invoice_number) AS (
> UPDATE invoice_numbers
> SET invoice_number = invoice_number + 1
> WHERE country = $1
> RETURNING invoice_number
> ),
> i (invoice_number) AS (
> INSERT INTO invoice_numbers (country, invoice_number)
> SELECT $1, 1
> WHERE NOT EXISTS (TABLE u)
> ON CONFLICT (country) DO
> UPDATE SET invoice_number = invoice_numbers.invoice_number + 1
> RETURNING invoice_number
> )
> TABLE u UNION ALL TABLE i;

Actually this is probably some premature optimization that you don't
need. Just the insert should be good enough.

INSERT INTO invoice_numbers (country, invoice_number)
VALUES ($1, 1)
ON CONFLICT (country) DO
UPDATE SET invoice_number = invoice_numbers.invoice_number + 1
RETURNING invoice_number;

--
Vik Fearing