Обсуждение: Invoice increment

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!"

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