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

Question

我有下表：

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

该表包含许多树，最多包含大约 1000 个节点。

(我能够通过递归查询有效地查询树及其后代(。

但是，我需要能够在一次操作中复制单个树。假设我有一棵有 3 个节点的树，ids 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(？

Answer 1

你去吧：

---

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)