Re: Restart increment to 0 each year = re-invent the sequences

Clodoaldo Pinto Neto wrote:
> 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(at)baguette(dot)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