Re: How to generate unique invoice numbers foreach day

In that case you have to manage the IDs on your own, the sequences won't
help you in this (unless you really create one sequence for each day,
which does not seem like a good solution to me).

A really simple solution might be to do a BEFORE INSERT trigger that
checks the last ID inserted for the day, locks that ID somehow (e.g. the
record with the ID) so that you don't get duplicate IDs and uses that ID.

Another solution is to manage the IDs on your own in a separate table,
i.e. create a table

CREATE TABLE invoice_numbers (
   invoice_date DATE NOT NULL PRIMARY KEY,
   invoice_last INTEGER NOT NULL DEFAULT 0
);

and then something like

BEGIN;

   UPDATE invoice_numbers SET invoice_last = (invoice_last + 1)
    WHERE invoice_date = '2010-11-...' RETURNING invoice_last;

   ... use the new invoice_last value

COMMIT;

But again, this might be a concurrency bottleneck if there's a lot of
invoices created at the same time by different users (or if the
transaction is long).

Tomas

Dne 15.1.2011 21:04, Andrus Moor napsal(a):
> Invoices can entered also some days forward or back. Users enters
> invoice date and expected program to generate next sequential number for
> this day.
> Different users can enter invoices for different days.
>
> Andrus.
>
>
>     ----- Original Message -----
>     *From:* Jorge Godoy <mailto:jgodoy@gmail.com>
>     *To:* Andrus Moor <mailto:kobruleht2@hot.ee>
>     *Cc:* pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>
>     *Sent:* Saturday, January 15, 2011 9:32 PM
>     *Subject:* Re: [GENERAL] How to generate unique invoice numbers
>     foreach day
>
>     Why would you do that?
>
>     You can always reset the sequence at the end of the day.
>
>     --
>     Jorge Godoy     <jgodoy@gmail.com <mailto:jgodoy@gmail.com>>
>
>
>     On Sat, Jan 15, 2011 at 17:09, Andrus Moor <kobruleht2@hot.ee
>     <mailto:kobruleht2@hot.ee>> wrote:
>
>         There are 365 days in year.
>         Do you really think pre-creating sequence for every day for
>         every year is best solution ?
>
>         Andrus.
>
>             ----- Original Message -----
>             *From:* Jorge Godoy <mailto:jgodoy@gmail.com>
>             *To:* Andrus Moor <mailto:kobruleht2@hot.ee>
>             *Cc:* pgsql-general@postgresql.org
>             <mailto:pgsql-general@postgresql.org>
>             *Sent:* Saturday, January 15, 2011 8:41 PM
>             *Subject:* ***SPAM*** Re: [GENERAL] How to generate unique
>             invoice numbers foreach day
>
>             Use a sequence.
>
>             --
>             Jorge Godoy     <jgodoy@gmail.com <mailto:jgodoy@gmail.com>>
>
>
>             2011/1/15 Andrus Moor <kobruleht2@hot.ee
>             <mailto:kobruleht2@hot.ee>>
>
>                 Invoice numbers have format  yymmddn
>
>                 where n is sequence number in day staring at 1 for every
>                 day.
>
>                 command
>
>                 SELECT COALESCE(MAX(nullif(substring( substring(tasudok
>                 from 7), '^[0-9]*'),'')::int),0)+1
>                 FROM invoice
>                 where date= ?invoicedate
>
>                 is used to get next free invoice number if new invoice
>                 is saved.
>
>                 If multiple invoices are saved concurrently from
>                 different processes, they will probably get same number.
>
>                 How to get unique invoice number for some day in 8.1+
>                 when multiple users create new invoices ?
>
>                 Andrus.
>
>                 --
>                 Sent via pgsql-general mailing list
>                 (pgsql-general@postgresql.org
>                 <mailto:pgsql-general@postgresql.org>)
>                 To make changes to your subscription:
>                 http://www.postgresql.org/mailpref/pgsql-general
>
>
>