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