Re: Queries for Diagramming Schema Keys

On Mon, Aug 14, 2017 at 6:38 PM, <stimits(at)comcast(dot)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.