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 <gordon.mcvey@ntlworld.com> wrote:
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.
If your primary key is a column named 'id' of type 'serial', you can
copy a record like this:
INSERT INTO my_table
SELECT nextval('table_name_id_seq'), foo, bar, baz, ...
FROM my_table
WHERE id = <id of original record to be copied>
--
Lars Haugseth
"If anyone disagrees with anything I say, I am quite prepared not only to
retract it, but also to deny under oath that I ever said it." -Tom Lehrer
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)