Is there a way to clone a row in a "maintainable" way (with PostgreSQL)?

Question

I often have to clone rows in my applications so I do it the long way :

INSERT INTO mytable (a, b, c)
SELECT a,b,c FROM mytable WHERE id=12;

(id is declared as a SERIAL PRIMARY KEY, 12 being the row to clone).

The thing is, when I add new fields to mytable I must remember to update the clone functions to also clone those new fields.

I would like to clone rows in a more maintainable way. Something like :

INSERT INTO mytable
SELECT *, id:DEFAULT FROM mytable WHERE id=12;

As I write it, I also think it would be nice to clone detail rows : if I have a detail/child table declared with referential integrity like this :

CREATE TABLE mychild (
 id serial PRIMARY KEY,
 parentid int REFERENCES mytable(id),
 a text
);

I could clone mytable and all childs in one command rather than :

1. clone mytable (the long way)
2. get the new id
3. clone all mychild, setting parentid to the new id

Is there anything in PostgreSQL (or extensions) that could help dealing with these cases ?

Answer 1

With the additional module hstore installed, there is a very simple way to replace the value(s) of individual field(s) without knowing anything about other columns:

INSERT INTO mytable
SELECT (t1).* -- note the parentheses
FROM (
 SELECT t #= hstore('id', nextval(pg_get_serial_sequence('mytable', 'id'))::text) AS t1
 FROM mytable t
 WHERE id = 12
 ) sub;

All columns are cast to text and back in the process.

Closely related answer with detailed explanation and more (possibly a bit faster) alternatives:

• Duplicate row with Primary Key in PostgreSQL

Clone parent row and all its children

That's more tricky. But based on the above, plus using a data-modifying CTE, there is a very elegant solution:

WITH ins_parent AS (
 INSERT INTO mytable
 SELECT (p).* -- note the parentheses
 FROM (
 SELECT t #= hstore('id', nextval(pg_get_serial_sequence('mytable', 'id'))::text) AS p
 FROM mytable t
 WHERE id = 2
 ) sub
 RETURNING id
 )
INSERT INTO mychild
SELECT (c).*
FROM (
 SELECT t #= hstore('id', nextval(pg_get_serial_sequence('mychild', 'id'))::text)
 #= hstore('parentid', (SELECT id::text FROM ins_parent)) AS c
 FROM mychild t
 WHERE parentid = 2
 ) sub;

This uses the new parentid for all copies in the child table and assigns new serial values to the id column.

Answer 2

This solution is completely derived from the one by Erwin. It clones a parent row and all its children using jsonb instead of hstore with a single query without knowing the name of the fields except the name of the primary key field.

WITH ins_parent AS (
 INSERT INTO mytable
 SELECT *
 FROM jsonb_populate_record(null::mytable, (
 SELECT
 row_to_json(t)::jsonb ||
 (
 '{"id": "'||nextval(pg_get_serial_sequence('mytable', 'id'))::text||'"}'
 )::jsonb as p
 FROM mytable t
 WHERE id = 2
 ))
 RETURNING id
)
 INSERT INTO mychild
 SELECT (jsonb_populate_record(null::mychild, t.c)).*
 FROM (
 SELECT
 row_to_json(sub)::jsonb ||
 (
 '{"id": "' ||nextval(pg_get_serial_sequence('mychild', 'id'))::text||'",
 "parentid":"'||(SELECT id::text FROM ins_parent)||'"}'
 )::jsonb as c
 FROM mychild sub
 WHERE parentid = 2
 ) t