Re: Locking of auto generated sequence

sid tow wrote:
> I am using a 7.2 version of postgreSQL and here if I have to copy
> data in a particular table then I cannot specify the columns I want
> to update, but rather i will have to copy data into all the columns
> present. Now the problem is that I have a auto generated sequence
> starting from 1 in one of the columns. If I try to copy the data into
> this column (numerically incrementing numbers ie same as the auto
> seq) also then i cannot get the last value of the sequence number
> which I can get by select last_value from seq;.

That's because the sequence value hasn't been updated. If you're
supplying your own numbers you should do something like:

BEGIN;
LOCK my_table IN EXCLUSIVE MODE;
SELECT setval('my_sequence_name', max(my_id_column)) FROM my_table;
COMMIT;

> But if I insert data
> in the columns other than this one then the sequence auto increments
> and I get the value in my next run. Can you tell me why is it
> happening this way and is there any solution so that i use only copy
> commands to update the data.

The sequence gets incremented when the column has no value and its
DEFAULT gets used. The default is nextval() which increments the
sequence and returns its value.

HTH
--
Richard Huxton
Archonet Ltd