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

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(at)postnewspapers(dot)com(dot)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
>
>