Skip site navigation (1) Skip section navigation (2)

Re: Problem with Autogenerated sequence

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: sid tow <siddy_tow(at)yahoo(dot)com>
Cc: psql mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Problem with Autogenerated sequence
Date: 2005-01-31 11:52:53
Message-ID: 20050131115250.GG25751@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
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(at)svana(dot)org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

pgsql-general by date

Next:From: Mark BattersbyDate: 2005-01-31 12:18:38
Subject: Dynamic SQL
Previous:From: NTPTDate: 2005-01-31 11:44:14
Subject: Postgresql and Athlon64 ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group