Re: Duplicating a table row while honouring key constraints

Gordon wrote:

> At first I thought INSERT INTO table_name SELECT * from table_name
> where primary_key = unique_value would do it, but that would obviously
> violate the primary key uniqueness constraint.  I'm wondering if
> there's a way to do this where I only grab the data to be copied and
> let the database work out the new primary key itself.

Well, try
INSERT INTO table_name SELECT col1, col2, ... FROM table_name WHERE
primary_key = unique_value

where the colX list excludes the primary key columns.  Perhaps add
DEFAULT specification for those so that they are generated from a
sequence or whatever default generator you have.

--
Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"A wizard is never late, Frodo Baggins, nor is he early.
 He arrives precisely when he means to."  (Gandalf, en LoTR FoTR)