时间戳

首页 > 解决方案 > 在 postgres 中有效地复制用邻接表建模的树

postgresql - 在 postgres 中有效地复制用邻接表建模的树

问题描述

我有下表：

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

该表包含许多具有最多约 1000 个节点的树。

（我能够使用递归查询有效地查询树及其后代）。

但是，我需要能够在一次操作中复制一棵树。假设我有一棵树，它有 3 个节点，ID 为 1,2,3（这可能是一棵大树）。我想复制它，即创建具有新 ID 的新节点。（这里复制的树是 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

有没有办法比单独插入每个树节点更有效地复制树及其后代（因为需要新的 parent_id）？

标签： postgresqllinked-listadjacency-list

解决方案

你去：

\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) )
        ;

输出：

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)

推荐阅读