Обсуждение: BUG #6084: When Updating Tables with Select Into

The following bug has been logged online:

Bug reference:      6084
Logged by:          Stewart Fritts
Email address:      stewart.fritts@gmail.com
PostgreSQL version: 8.4
Operating system:   Windows Server 2003
Description:        When Updating Tables with Select Into
Details:

First, I love your database. I have been using it for a few years now and it
rarely lets me down while regularly surprising me with how powerful it truly
is.

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.

It was an easy enough fix, I just changed the current value of the sequence
causing the issue. But I thought it was worth mentioning that this happened.

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

Thank you for responding.

Shortly after writing the email, I realized what I had done.
I did not use the wildcard*, but rather specified each column with its name.
However, the tables which were being moved contained foreign keys relating
to one another. Therefore the primary key had to be transfered intact.
My perception of how the sequences incremented was obviously flawed.

On Wed, Jun 29, 2011 at 7:34 PM, Craig Ringer
<craig@postnewspapers.com.au>wrote:

> 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
>
>