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.
alcionebernardiAsked:
Who is Participating?
 
Alex MatzingerConnect With a Mentor Database AdministratorCommented:
The easiest way to do this, is to drop the constraints/foreign keys from the tables in your postgres database, import the data (from oracle to postgres) and then re-add the constraints to the tables.  

Otherwise you will need to go by hand through each of your tables and list them in the order they will need to be.  There is no automatic way of listing tables by their constraints.
0
 
bayoubeastCommented:
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.
0
 
alcionebernardiAuthor Commented:
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
0
 
bayoubeastCommented:
Yeah, that's probably the best way to do this.  If you're using ora2pg it has some options for this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.