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

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 :

• clone mytable (the long way)

• get the new id

• clone all mychild, setting parentid to the new id

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

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.

StackExchange Database Administrators Q#148908, answer score: 3