Re: [GENERAL] Queries for Diagramming Schema Keys - Mailing list pgsql-general
On Mon, Aug 14, 2017 at 6:38 PM, <stimits@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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
wish to share my fantasy is entirely up to you.
pgsql-general by date: