Link to home
Start Free TrialLog in
Avatar of alcionebernardi
alcionebernardiFlag for Brazil

asked on

dependency tree of tables in PostgreSQL

Hello Experts

how can I get the list of the postgresql tables in order of dependency tables.
Problem: I need to make a  migration routine of data from one oracle database to PostgreSQL. The data model is equal.
Avatar of bayoubeast
bayoubeast

In psql you could do something like this:

select '\\d+ '||table_schema||'.'||table_name
from information_schema.tables
order by table_schema,table_name;

Open in new window


And if you run the output from that back into psql that will list the dependencies for each table individually.  You'll probably want to add a where clause for the specific schema you want to look at so you don't get all the postgresql tables.

select '\\d+ '||table_schema||'.'||table_name
from information_schema.tables
where table_schema='public'
order by table_schema,table_name;

Open in new window


Is this kind of what you're looking for?  I'm not quite sure if there's a way to make a single list of tables ordered by dependency.
Avatar of alcionebernardi

ASKER

Hello,
    for example, two tables: d_m_transaction and m_transaction. d_m_transaction has references to m_transaction. I would like a sql that return the two tables in the following order

m_transaction
d_m_transaction

That way I could run the inserts without any violation of constraints

thanks
ASKER CERTIFIED SOLUTION
Avatar of Alex Matzinger
Alex Matzinger
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yeah, that's probably the best way to do this.  If you're using ora2pg it has some options for this.