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.