On Mon, Aug 14, 2017 at 6:38 PM, <stim...@comcast.net> wrote: > ... > > Just to add that running psql with the -E switch is REALLY handy for > seeing how psql executes queries to > > find how tables etc are put together. > > I can't actually use that feature to gather the information I'm interested > in since all I have are tables and data with no formal information on key > relations and allowed load order. There is so much data in so many tables > that testing even a single load iteration takes many hours and there are > literally many thousands of load order combinations possible. Logs of > hundreds of thousands (or millions) of XML loads would take a very long > time to go through, and would then only give one possible load order. > > Automating a diagram of key relations and visualizing it is the first step > to methodically computing a correct load order, but I can't do that until I > figure out how to use the system tables to describe (1) columns which are > not keys, (2) columns which are primary keys not referring to another > column, and (3) columns which are foreign keys and the table/column they > are pointed at. My SQL knowledge is somewhat limited and I am struggling > with the system tables. >
*For * *> (3) columns which are foreign keys and the table/column they are pointed a* *This should do the trick, you can tweak as needed.* *SELECT nsp.nspname, rel.relname, con.conname, con.contype, pg_get_constraintdef(con.oid, true) FROM pg_class rel JOIN pg_namespace nsp ON (nsp.oid = rel.relnamespace) JOIN pg_constraint con ON (con.conrelid = rel.oid) WHERE contype = 'f' AND rel.relname = 'account' ORDER by relname, contype, conname;* *However, for the others, I have no intention of creating the queries for you. I encourage you to learn the PostgreSQL system catalogs.* *You have not provided us with the version of PostgreSQL you are using, so I'll just point you to the relevant part in the latest doc.* *https://www.postgresql.org/docs/9.6/static/catalogs.html <https://www.postgresql.org/docs/9.6/static/catalogs.html>* *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
