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

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