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

Re: BUG #6084: When Updating Tables with Select Into

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Stewart Fritts <stewart(dot)fritts(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6084: When Updating Tables with Select Into
Date: 2011-06-30 00:34:52
Message-ID: 4E0BC4AC.7040906@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-bugs
On 29/06/2011 10:19 PM, Stewart Fritts wrote:
> I recently migrated some data from one set of tables to another within a
> single schema. I did this by using a select...into query. Everything moved
> perfectly. However; I noticed that the sequence for the receiving table did
> not increment with the newly added data.
In general it's not expected to if the sequence wasn't used to generate 
the IDs of the values inserted into the target table.

Can you show the SQL command you used? I'm guessing you used "SELECT * 
...." rather than specifying a column-list that omits the generated 
column, specifies it with value DEFAULT, or specifies it with 
valuenextval('seq_name') . If you use 'SELECT *' then the target table's 
sequence is never used because you specified that the primary key should 
be copied from the old table along with all the rest of the data.

If you want to re-generate keys, instead of using:

SELECT * INTO ... FROM ....

use:

INSERT INTO ... (col1,col2,col3,col4)
SELECT col1, col2, col3, col4, ... FROM ...

and omit the PK column from the column-list so that the default value is 
used during INSERT.

--
Craig Ringer


In response to

Responses

pgsql-bugs by date

Next:From: Craig RingerDate: 2011-06-30 00:41:58
Subject: Re: BUG #6063: compatability issues
Previous:From: Tom LaneDate: 2011-06-29 23:45:46
Subject: Re: BUG #6079: Wrong select result

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