You can't. Sequences are not designed for continuity, they are designed for uniqueness. If you want to have a set of contiguous numbers, in ascending order, then you will probably have to write a trigger to insert the next value, which it has to scan the table to work out. And you have to decide what to do in case of deletions: do you reuse the number on the next insert (add complexity and run-time to the code), or just carry on anyway, meaning that you have holes in your sequence, in which case, you could have used a sequence anyway, probably. Depending on the number of expected rows in the table, you may find that the time to insert doesn't justify having contiguous numbers. For each insert, the minimum you are going to get away with is a full table scan.
MikeA
>> -----Original Message-----
>> From: Elmar.Haneke@gmx.de [mailto:Elmar.Haneke@gmx.de]
>> Sent: 27 April 2000 09:39
>> To: pgsql-general@postgresql.org
>> Subject: [GENERAL] sequences and Transactions
>>
>>
>>
>>
>> > ----------
>> > From: Elmar.Haneke@gmx.de[SMTP:ELMAR.HANEKE@GMX.DE]
>> > Sent: Thursday, April 27, 2000 10:38:55 AM
>> > To: pgsql-general@postgresql.org
>> > Subject: [GENERAL] sequences and Transactions
>> > Auto forwarded by a Rule
>> >
>> Hi!
>>
>> how can I setup sequences to have the current-value reset in case
>> of an Transaction rollback.
>>
>> My intension is to get an contignous numbering of the rows.
>> Currently in case of an Rollback one number is skipped since
>> the record itself is not inserted but the counter is not reset.
>>
>> Elmar
>>
