At 11:20 PM 11/24/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>From: ries van Twisk <pg(at)rvt(dot)dds(dot)nl>
>To: Tk421 <vrobador(at)gmail(dot)com>
>Subject: Re: Sequence and nextval problem
>Date: Mon, 24 Nov 2008 16:21:40 -0500
>On Nov 24, 2008, at 2:12 PM, Tk421 wrote:
>> The conversion from access database to postgres worked fine.
>>Everithing it's ok. But now, when i use my database i've found a
>>problem with sequences. In the conversion, the "autonumeric" fields
>>from access have been converted to sequences, everithing ok in a
>>first view. The problem comes because the autonumeric fields in
>>access always return the last value of the table +1, but postgres
>>no. Postgres returns "lost" (i don't know how to call them) values.
>> In access if i execute "INSERT INTO table (description) VALUES
>>('desc 8'), the result row is 8 | desc 8
>> But in postgres the same query te result row is 3 | desc 8
>> My question is, can i do something to make ANY sequence to take
>>the last value from his associated table, and not a "lost" value?
>This sounds like if the start of the sequence is set incorrectly:
>Try this : SELECT setval('NAME OF SEQUENCE', SOME_INTEGER, true);
>btw, you should also not expect a specific value from the sequence
>except that you will always get the next value from the sequence.
>it's also generally a bad idea to do select max(someid)+1 from
>The whole concept of a sequence is thus much better.
I think this is sound general advice for a production database.
However if you control the database such that you can prevent access to
it while you are updating it, you can run something like:
SELECT setval('NAME OF SEQUENCE', (select max(id)+1 from
Where "table_of_sequence" is the name of the table which the sequence
is attached to.
The reason you don't use that syntax is that it's not multi-user safe.
But if you know there are no other users running changes to that
sequence when you run your updates, then you're good to go. It's a very
fast way to update all your tables to make sure the sequence #'s are
all valid, without having to look up the max value on each one (which
would also require that you shut off access to the table and for a much
Hope that helps,
pgsql-sql by date
|Next:||From: Oleg Bartunov||Date: 2008-11-25 16:43:16|
|Subject: Re: Full text search ordering question|
|Previous:||From: John Lister||Date: 2008-11-25 15:28:11|
|Subject: Full text search ordering question|