Postgresql copy data within the tree table

Question

I have table with tree structure, columns are id, category, parent_id

Now I need a copy a node and its child's to a another node, while copying, the category must be same, but with new id and parent_id..

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

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

i need a function to do so..,

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)

help me with function which get 2 inputs and does the job... — MAHI, Apr 05 '12 at 08:33
So basically you want to clone a subtree? Hard. I'm thinking on it. — wildplasser, Apr 05 '12 at 10:00
yes its hard.. i have tried out with many function.. no use.. — MAHI, Apr 05 '12 at 10:04
I think it can be done, but You'll need at least one bit of "state" in the table. Do you have a spare column for that ? (could be done in a separate table, too) — wildplasser, Apr 05 '12 at 11:43
can anyone help me with [this](http://stackoverflow.com/questions/9077266/postgresql-update-tree-table-with-rows-from-same-table-by-changing-id-andparen) — MAHI, Apr 06 '12 at 05:01

Branko Dimitrijevic · Accepted Answer · 2012-04-05T20:53:37.320

(tested under PostgreSQL 8.4.3)

The following query assigns new IDs to the sub-tree under node 4 (see the nextval) and then finds the corresponding new IDs of parents (see the 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 (on your test data):

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

Once you have that, it's easy to insert it back to the table, you just have to be careful to reconnect the sub-tree root with the appropriate parent (8 in this case, see the 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 that, 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

This is brilliant. I did not expect the recursive+next combo could do the trick without needing an extra state variable. Chapeau! — wildplasser, Apr 05 '12 at 16:33
can anyone help me with [this](http://stackoverflow.com/questions/9077266/postgresql-update-tree-table-with-rows-from-same-table-by-changing-id-andparen) — MAHI, Apr 06 '12 at 05:00

Postgresql copy data within the tree table

1 Answers1

Linked