Re: Duplicating a table row while honouring key constraints

* Gordon <gordon(dot)mcvey(at)ntlworld(dot)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