If the gaps (user gets a number from a sequence and then rollbacks the transaction) are not a problem, then the sequences (reset every day) are probably the best solution.
If the gaps are a problem (which is usually the case with invoicing systems), then you need to manage that on your own, e.g. using a table with one row for sequence (but that might be a bottleneck with multiple users and a lot of invoices). Tomas Dne 15.1.2011 20:32, Jorge Godoy napsal(a): > Why would you do that? > > You can always reset the sequence at the end of the day. > > -- > Jorge Godoy <jgo...@gmail.com <mailto:jgo...@gmail.com>> > > > On Sat, Jan 15, 2011 at 17:09, Andrus Moor <kobrule...@hot.ee > <mailto:kobrule...@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:jgo...@gmail.com> > *To:* Andrus Moor <mailto:kobrule...@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 <jgo...@gmail.com <mailto:jgo...@gmail.com>> > > > 2011/1/15 Andrus Moor <kobrule...@hot.ee <mailto:kobrule...@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 > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
