Обсуждение: 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 > >