Problem with Autogenerated sequence

Inserting into all the columns doesn't change the sequence, as you
discovered. The usual solution to this problem is to create a table
that exactly matches your to-be-COPYed-data, do the copy.

CREATE TEMP temp_table (columns from data...)
\copy temp_table from <wherever>
INSERT INTO real_table (columns...) SELECT * FROM temp_table;

-- temp_table will be automatically deleted at end of session or you
-- can drop it explicitly.

This will trigger the sequence as normal...

Alternativly you can use setval() to update the sequence...

Hope this helps,

On Mon, Jan 31, 2005 at 02:20:03AM -0800, sid tow wrote:

Hi,

I have a problem with the SEQUENCE generation. I have a column in
a table which auto increments by 1 every time there is a entry
and I am strictly prohibited to use only PostgreSQL 7.2 version.
Now the problem is with the COPY command, I will have to copy
entire columns present in the table from a file with this version
of PostgreSQL. I just cant copy those fields I want to update.
For this what I did was I got the current value of the auto
generated sequence from ie the column "last_value" from the
sequence table and updated the auto generating column also along
with the other columns. Now the problem is that even after I have
inserted some data in the tables I see no change in the column
last_value of the sequence table since last copy or insert of
data. Why is it? And is there any alternate way to copy only
those columns I need to using this version of PostgreSQL?

Note: I referred man pages of create_sequence to get the information.

Regards,
Sid

---------------------------------
Do you Yahoo!?
Yahoo! Search presents - Jib Jab's 'Second Term'

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/