Re: Invoice increment

On 26/02/2020 09:38, Søren Frisk wrote:
> Hi all
>
> I'm trying to find a way to increment an invoice number. And i think it
> would be pretty straight forward just using a SERIAL int. But as we're
> selling across multiple countries, i need the invoice increment to be reset
> by a country_id. any good way to achieve this in a dynamic way?

A serial column is absolutely not a good fit for invoice numbers because
in some (most? all?) countries, the numbers are not allowed to have gaps.

The way I would do it is have an invoice_numbers table like this:

CREATE TABLE invoice_numbers (
    country text PRIMARY KEY REFERENCES countries,
    invoice_number bigint NOT NULL
);

And then you can get the next number with something like this:

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;

You can just put that in an SQL function for convenience.

> Hope this is the right place to ask.

Yes, it is.
--
Vik Fearing