Postgresql copies data to a tree table

Question

I have a tree structure table, columns id , category , parent_id

Now I need a copy of the node and its child in another node, when copying the category should be the same, but with a new id and parent_id ..

My input will be node to copy and destination node to copy

I explained the structure of the tree in the image file.

I need a function for this ..,

PostgreSQL version 9.1.2

  Column | Type | Modifiers -----------+---------+------------------------------------------------- id | integer | not null default nextval('t1_id_seq'::regclass) category | text | parent_id | integer | Indexes: "t1_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "fk_t1_1" FOREIGN KEY (parent_id) REFERENCES t1(id) Referenced by: TABLE "t1" CONSTRAINT "fk_t1_1" FOREIGN KEY (parent_id) REFERENCES t1(id) 

Answer 1

(tested in PostgreSQL 8.4.3)

The following query assigns new identifiers to the subtree under node 4 (see nextval ), and then finds the corresponding new parent IDs (see LEFT JOIN ).

 WITH RECURSIVE CTE AS ( SELECT *, nextval('t1_id_seq') new_id FROM t1 WHERE id = 4 UNION ALL SELECT t1.*, nextval('t1_id_seq') new_id FROM CTE JOIN t1 ON CTE.id = t1.parent_id ) SELECT C1.new_id, C1.category, C2.new_id new_parent_id FROM CTE C1 LEFT JOIN CTE C2 ON C1.parent_id = C2.id 

Result (according to your test data):

 new_id category new_parent_id ------ -------- ------------- 9 C4 10 C5 9 11 C6 9 12 C7 10 

Once you have this, it's easy to insert it back into the table, you just need to be careful to reconnect the subtree root with the appropriate parent (in this case 8 (see COALESCE(new_parent_id, 8) )):

 INSERT INTO t1 SELECT new_id, category, COALESCE(new_parent_id, 8) FROM ( WITH RECURSIVE CTE AS ( SELECT *, nextval('t1_id_seq') new_id FROM t1 WHERE id = 4 UNION ALL SELECT t1.*, nextval('t1_id_seq') new_id FROM CTE JOIN t1 ON CTE.id = t1.parent_id ) SELECT C1.new_id, C1.category, C2.new_id new_parent_id FROM CTE C1 LEFT JOIN CTE C2 ON C1.parent_id = C2.id ) Q1 

After this, the table contains the following data:

 new_id category new_parent_id ------ -------- ------------- 1 C1 2 C2 1 3 C3 1 4 C4 2 5 C5 4 6 C6 4 7 C7 5 8 C8 3 9 C4 8 10 C5 9 11 C6 9 12 C7 10