Efficiently copying a tree modelled with adjacency list in postgres

Question

I have the following table:

CREATE TABLE tree_node (
    id serial primary key,
    name character varying(255),
    parent_id integer references tree (id)
);

The table contains many trees with up to about 1000 nodes.

(I'm able to query a tree and its descendants efficiently with a recursive query).

However, I need to be able to copy a single tree in one operation. Say I have a tree with 3 nodes, ids 1,2,3 (this is potentially a large tree). I would like to make a copy of it i.e. creating new nodes with new ids. (Here the copied tree is ids 4,5,6):

id |      name       | parent_id
----+-----------------+-----------
  1 | NodeA           |
  2 | NodeA.1         |         1
  3 | NodeA.1.1       |         2
  4 | NodeA(copy)     |
  5 | NodeA.1(copy)   |         4
  6 | NodeA.1.1(copy) |         5

Is there a way to copy a tree and its descendants more efficiently than inserting each tree node separately (because the new parent_id is needed)?

Copy the three to *what*? TO a table? to a file? FROM a file? — wildplasser, Feb 01 '20 at 12:49
Also, are you wanting to keep the current key values or map to new keys? — Belayer, Feb 01 '20 at 22:25
Updated the question with better explanation of what I meant by "copy" — a_b, Feb 02 '20 at 12:24
Consider this trick: https://stackoverflow.com/a/29264127/1048572 — Bergi, Feb 03 '20 at 00:27

score 0 · Answer 1 · answered Feb 02 '20 at 12:55

There you go:

\i tmp.sql

CREATE TABLE tree_node (
    id serial primary key
    , name varchar
    , parent_id integer references tree_node (id)
);


INSERT INTO tree_node(name, parent_id) VALUES
        ( 'Node-A', NULL)
        , ( 'Node-A.1', 1)
        , ( 'Node-A.1.1', 2)
        ;
SELECT * FROM tree_node;

        -- Find the top value of the sequence
        -- and use it as an increment on all the copies
WITH top(val) AS
        (select currval('tree_node_id_seq')
        )
INSERT INTO tree_node(id, name, parent_id)
SELECT id+top.val
        , name|| '(copy)'
        , parent_id + top.val
FROM tree_node
CROSS JOIN top
        ;

SELECT * FROM tree_node;

        -- bump the sequence
WITH nxt AS (
        select max(id) mx from tree_node
        )
SELECT setval('tree_node_id_seq', (select mx FROM nxt) )
        ;

Output:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 3
 id |    name    | parent_id 
----+------------+-----------
  1 | Node-A     |          
  2 | Node-A.1   |         1
  3 | Node-A.1.1 |         2
(3 rows)

INSERT 0 3
 id |       name       | parent_id 
----+------------------+-----------
  1 | Node-A           |          
  2 | Node-A.1         |         1
  3 | Node-A.1.1       |         2
  4 | Node-A(copy)     |          
  5 | Node-A.1(copy)   |         4
  6 | Node-A.1.1(copy) |         5
(6 rows)

 setval 
--------
      6
(1 row)

Efficiently copying a tree modelled with adjacency list in postgres

1 Answers1