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

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.