Re: Sequence-table association

Vamsee,

> I don't know if this is the right place, but I have a question about
> postgres sequences. Does anyone here know if there is a way to associate a
> sequence with a table (for primary keys) so that when inserts are made in
> the table even without using the sequence, its nextval is automatically
> updated? Even the serial datatype doesn't help much because the sequence is
> not auto-updated when it is not used.
>
> So the bottomline of my problem is to be able to choose between having the
> sequence generate a number for my primary key, and providing one myself
> without ever getting yelled at for trying to insert a duplicate key.

Sounds like you have some application design problems rather than sequence
problems ;-)

If you need the client application to have the ID before the record is
inserted, simply have the application call:

SELECT NEXTVAL('name_of_sequence');

.... which will provide you the new ID and increment the sequence,
guarenteeing uniqueness, even if the transaction is rolled back.

--
-Josh Berkus
Aglio Database Solutions
San Francisco