Thread: copy row tree

copy row tree

From

"Toni Casueps"

Date:

17 January 2007, 06:47:15

I have a set of tables with one-to-many relationships between them:

T1 <-->> T2 <-->> T3 <-->> T4

I need to copy some rows of these tables to another set of tables which have
the same fields. There are two rows on T1 that I want to copy, and then
those rows of T2 linked to the other ones, and so on.

Is there a way to accomplish this? I have pgadminIII, DBManager and EMS
Postgresql Manager.

_________________________________________________________________
Grandes éxitos, superhéroes, imitaciones, cine y TV...
http://es.msn.kiwee.com/ Lo mejor para tu móvil.

Re: copy row tree

From

Richard Huxton

Date:

17 January 2007, 08:24:22

Toni Casueps wrote:
>
> I have a set of tables with one-to-many relationships between them:
>
> T1 <-->> T2 <-->> T3 <-->> T4
>
> I need to copy some rows of these tables to another set of tables which
> have the same fields.

There's no shortcut.

BEGIN;
INSERT INTO copy_t1 SELECT * FROM t1 WHERE id IN (123, 456);
INSERT INTO copy_t2 SELECT * FROM t2 WHERE t2_t1_ref IN (SELECT id FROM
t1 WHERE id IN (123, 456));
INSERT INTO copy_t3 SELECT * FROM t3 WHERE t3_t2_ref IN (SELECT ... FROM
t2 WHERE ...)
...etc...
COMMIT;

--
   Richard Huxton
   Archonet Ltd

Re: copy row tree

From

"Merlin Moncure"

Date:

18 January 2007, 00:41:40

On 1/17/07, Richard Huxton <dev@archonet.com> wrote:
> Toni Casueps wrote:
> >
> > I have a set of tables with one-to-many relationships between them:
> >
> > T1 <-->> T2 <-->> T3 <-->> T4
> >
> > I need to copy some rows of these tables to another set of tables which
> > have the same fields.
>
> There's no shortcut.
>
> BEGIN;
> INSERT INTO copy_t1 SELECT * FROM t1 WHERE id IN (123, 456);
> INSERT INTO copy_t2 SELECT * FROM t2 WHERE t2_t1_ref IN (SELECT id FROM
> t1 WHERE id IN (123, 456));
> INSERT INTO copy_t3 SELECT * FROM t3 WHERE t3_t2_ref IN (SELECT ... FROM
> t2 WHERE ...)
> ...etc...
> COMMIT;

maybe there is:
set up RI triggers and set them to on update..cascade
add a trigger to each table which inserts into copy tables
if head table has p-key of id,

update main set id = id where id in (copy list);

:-)

merlin

Re: copy row tree

From

Michael Raven

Date:

19 January 2007, 19:28:38


Toni Casueps wrote:
>
>
> I have a set of tables with one-to-many relationships between them:
>
> T1 <-->> T2 <-->> T3 <-->> T4
>
> I need to copy some rows of these tables to another set of tables which
> have
> the same fields. There are two rows on T1 that I want to copy, and then
> those rows of T2 linked to the other ones, and so on.
>
> Is there a way to accomplish this? I have pgadminIII, DBManager and EMS
> Postgresql Manager.
>
>

Is there foreign keys constrains between tables T1, T2, T3 and T4 or just
logical relationship?
EMS Postgresql Manager has visual query designer and dependency viewer. Use
it features to select disired records and insert it into second set of
tables. Also
--
View this message in context: http://www.nabble.com/-GENERAL--copy-row-tree-tf3026502.html#a8445042
Sent from the PostgreSQL - general mailing list archive at Nabble.com.