Re: How to generate unique invoice numbers for each day - Mailing list pgsql-general

On 2011-01-15, Andrus Moor <kobruleht2@hot.ee> wrote:
> 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 ?

use a sequence, reset the sequence each night.

--
⚂⚃ 100% natural