You don't have to mess with sequences.
If there are two fields ID and year then the next number is:
next_number := ( select ID from table_name where year = year_from_current_date order by ID desc limit 1 ) + 1;
This could cause ID collision. If two seperate processes call this statement at the same time, they will get the same number, and when they try to insert their record, there will be a collision. One of the processes will error out.
Sequences exist to avoid this problem. A sequence _is_ the proper way to do this.
Regards, Clodoaldo
--- Bruno Baguette <pgsql-ml@baguette.net> escreveu: > Hello,
I have to design a table wich will store some action reports. Each report have an ID like this 1/2004, 2/2004, ... and each years, they restart to 1 (1/2004, 1/2005, 1/2006,...).
So, I was thinking to split that in two fields : one with the increment and one with the year. But I don't know how can I manage the sequences since I have to restart to 0 each year...
Do you think I should re-invent the sequences mecanism with a second table and a stored procedure, only to manage theses 'home-made' sequences ?
Or should I create some sequences like myseq_2004, myseq_2004, my_seq_2005,... and use a concatenation of the myseq_ string and the current year when calling nextval and curvall ?
Or is there another way to do that ?
Thanks in advance :-)
-- Bill Moran Potential Technologies http://www.potentialtech.com
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
