This looks to be the way to go! Thank you very much. I'll do the function in my application code to have it in version control. But it helped me wrap my head around it.
Den ons. 26. feb. 2020 kl. 10.27 skrev Vik Fearing <vik@xxxxxxxxxxxxxxxxxxx>:
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
