Re: Invoice increment

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"