Invoice increment

On Wed, 26 Feb 2020 at 09:54, Søren Frisk <soeren.frisk@gmail.com> 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?
Hope this is the right place to ask.

Hi,

Instead of a SERIAL field use INTEGER and different sequences for different
countries. You can write a trigger which picks the right sequence based on
the country_id and you can even use a trigger to making the new sequence
for any new country_id.
However it might cause problems if you are not cautious enough. Sequences
and serials not roll back their counter in case of a failing query, so you
could introduce holes into your invoice numbers, which can make supervising
authorities angry. :)

Regards,
Sándor

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

On 2020-02-26 09:38:57 +0100, Søren Frisk wrote:

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.

Be careful: Sequences may have gaps (e.g. because of rolled back
transactions, but maybe also for other reasons). In many jurisdictions,
invoice numbers must not have gaps. You may still be able to use
sequences, but you have to carefully consider when to increment them.

But as we're selling across
multiple countries, i need the invoice increment to be reset by a country_id.

By "reset by a country_id" you mean that the invoice number has to
increment independently for each country? So if you sell for example, to
DK, DK, CN, DK, CN, US, DK, US, the invoice numbers should be
DK-1
DK-2
CN-1
DK-3
CN-2
US-1
DK-4
US-2
?

You can do that by having one sequence per country. The parameter to
nextval is just a string, so you can construct that on the fly:

select nextval('invoice_seq_' || country_code);

However, that means that you have to add a new sequence whenever you
start business in a new country. Maybe not a big deal, but easy to
forget since you don't do that every day.

The other way is of course to have a table with all the current invoice
numbers for each country. Basically sequences implemented in the
application. This has a slightly different set of problems, so you have
to be careful, too.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

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@postgresfriends.org

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