From: | Vik Fearing <vik(at)postgresfriends(dot)org> |
---|---|
To: | Søren Frisk <soeren(dot)frisk(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Invoice increment |
Date: | 2020-02-26 09:27:21 |
Message-ID: | dba07b2f-7c81-a522-f03f-9f46e6127b78@postgresfriends.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2020-02-26 09:30:28 | Re: Invoice increment |
Previous Message | Sándor Daku | 2020-02-26 09:22:12 | Re: Invoice increment |