Clone a row with a primary key in SQL?

Answer 1

I'm not sure about PostgreSQL but if it supports the auto-increment like most systems do then just leave the primary key field blank. However this does mean you need to specify the column names.

Also, when you say clone it sounds like you want a duplicate of your record but in your description the address1 and address2 columns are on the same row of data.

If you want to replicate your address1 to the address2 column you can use a simple update statement.

If you are trying to clone the entire row to have two records that are exactly the same then you could use something like this and it should work on both environments.

INSERT INTO Addresses
( address1, address2 )
SELECT address1, address2
  FROM Addresses
 WHERE addressId = ID

Now the next question is, Are you cloning from one DB type to the other? If so then you will need to pull it into your app anyway. If you are then why don't you use something like nHibernate to abstract the db layer away from you.

If the goal is to just make a copy then you can do it straight in the db with the sql above.

While this sql is probably the easiest (and you could reflect it from the db if needed using system / master tables) you would still need to specify the column names. The primary reason is that as soon as you add the * wildcard it would still try to include your original primary key column in the select list which would then have 1 extra column to what you have.

Another issue to be aware of when using wildcards in insert statements is that your column orders HAVE TO MATCH otherwise you will get very unexpected results.

Answer 2

Postgres, like Oracle, uses sequences to generate sequencial numeric values for artificial/surrogate keys. In order to get the next value, you need to use:

NEXTVAL(your_sequence_name) 

...in your INSERT statement in order to set the value for the primary key. You won't be able to omit it from your INSERT statement, unless for some odd reason the column is nullable. So on Postgres you need to use:

INSERT INTO PrimKeys 
SELECT NEXTVAL(your_sequence_name),
       [rest of columns, because you can't include the pk col/value]
  FROM PrimKeys 
 WHERE PrimKey='PrimKey1'

Equivalent SQL Server Functionality

SQL Server (and MySQL) allow you to either not specify the column, or supply NULL as a placeholder for the primary key column and will provide the value for you. That's as close as you get.

Reference:

• Postgre Sequences