Thread: Duplicating a table row while honouring key constraints

I'm developing a web application in PHP and Postgres that will
basically serve as a CMS.  I want to implement a feature to allow
users to make copies of documents or folders, so this will require the
appropriate rows to be duplicated.

If possible I'd like to do this with SQL queries and avoid SELECTing
the row, munging it in PHP and INSERTING it back.  I suspect that this
is probably the way I'll have to go, but if it could be done entirely
in SQL that would be nice.

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.

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)