3

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 ?

2

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:

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.