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
