Queries for Diagramming Schema Keys

On Mon, Aug 14, 2017 at 2:46 PM, <stimits@comcast.net> wrote:

Hi,

I'm trying to write an application (using libpqxx/C++) which creates
graphical images of large and complex relations between tables (basically
an SVG image with hot spots for drilling down on details). I need to
essentially create icons of tables and their columns, and then draw
interconnecting lines where foreign keys are involved, and to distinctly
label primary keys, foreign keys, and non-key columns. Eventually this will
have knowledge of an XML file loading scheme and be able to reverse
engineer the required load order (imagine approximately 1,000 tables with
many foreign keys and file loads which may take hours for each failure to
load). I need some advice on using ANSI/information_schema queries to
accomplish this.

Thus I have this query to list all tables:

SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE';

...this seems to work ok. The next query is to find all foreign
keys...this seems to sort of work, but may have issues:

SELECT
tc.table_name AS local_table,
kcu.column_name AS key_column,
ccu.table_name AS fk_table,
ccu.column_name AS fk_column
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name
IN (SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE');

This is my query to find all primary keys which are not foreign keys, and
this definitely is not 100% correct:
SELECT DISTINCT
tc.table_name AS local_table,
kcu.column_name AS key_column
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name
IN (SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE')
AND (tc.table_name, kcu.column_name)
NOT IN (
SELECT
tc.table_name,
kcu.column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name
IN (SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE')
)
ORDER BY local_table, key_column;

I am completely at a loss how I would query for all columns which are
neither primary nor foreign keys. Would anyone have a suggestion for
something like this:
SELECT table_name, non_key_column
FROM ...
WHERE ...
ORDER BY table_name, non_key_column

Any advice on any of the queries would be appreciated!

Thanks!

I am completely at a loss how I would query for all columns

*Does this help?*

*-- TABLES AND COLUMNS*

*SELECT c.table_schema as schema, c.table_name as table,
c.ordinal_position as order, c.column_name as column, CASE WHEN
c.data_type IN ('character', 'varchar') THEN c.data_type || '(' ||
c.character_maximum_length || ')' WHEN TRIM(c.data_type) IN
('numeric') THEN c.data_type || '(' || c.numeric_precision_radix ||
',' *

* || c.numeric_scale || ')' ELSE c.data_type
END, c.is_nullable as null, col_description(t.oid,
c.ordinal_position) as comment FROM information_schema.columns c JOIN
pg_class t ON (t.relname = c.table_name) WHERE table_schema = 'public'
AND c.table_name = 'album'ORDER BY 1, 2, 3;*

*-- TABLES AND PKEYS*

*SELECT n.nspname, t.relname as table, c.conname as pk_name
FROM pg_class t JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype
= 'p') JOIN pg_namespace n ON (n.oid = t.relnamespace) WHERE relkind =
'r' AND t.relname NOT LIKE 'pg_%' AND t.relname NOT LIKE 'sql_%'
ORDER BY n.nspname, t.relname, c.conname;*

*-- TABLES and FKEYS*

*SELECT n.nspname as schema, t.relname as table, c.conname as
fk_name FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f') WHERE
relkind = 'r' AND t.relname NOT LIKE 'pg_%' AND t.relname NOT LIKE
'sql_%' ORDER BY n.nspname, t.relname, c.conname;*
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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.

...snip...
For all tables and columns I get no output at all. Basically I need all tables and columns for which the column is neither a regular primary key, nor a foreign key. This will be marked as an attribute in the diagramming program.

Your query for primary keys which are not foreign keys seems to be magic :) I'll have to dig through some of the tables (nearly 1000 of them) to validate, but it seems to work with much less complication than my original query (and I think my original had issues...I forgot to mention that I had been relying on the columns which implemented values which foreign keys had referenced were assumed to have the same column names...mostly this had been true, but not always, so my original approach was a guaranteed failure).

For foreign keys I need a row with both the table and column which is a foreign key, and the specific table and column it maps to. My mapping program will be adding a double-linked list among keys for validation and for interactive use of an SVG image being produced.

So I'm still looking for "original_table.fk_column->implementing_table.implementing_column", plus the list of "table.column" where "column" is not a key.

I am completely at a loss how I would query for all columns

Does this help?

-- TABLES AND COLUMNS
SELECT c.table_schema as schema, c.table_name as table, c.ordinal_position as order, c.column_name as column, CASE WHEN c.data_type IN ('character', 'varchar') THEN c.data_type || '(' || c.character_maximum_length || ')' WHEN TRIM(c.data_type) IN ('numeric') THEN c.data_type || '(' || c.numeric_precision_radix || ','
|| c.numeric_scale || ')' ELSE c.data_type END, c.is_nullable as null, col_description(t.oid, c.ordinal_position) as comment FROM information_schema.columns c JOIN pg_class t ON (t.relname = c.table_name) WHERE table_schema = 'public' AND c.table_name = 'album'ORDER BY 1, 2, 3;

-- TABLES AND PKEYS
SELECT n.nspname, t.relname as table, c.conname as pk_name FROM pg_class t JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'p') JOIN pg_namespace n ON (n.oid = t.relnamespace) WHERE relkind = 'r' AND t.relname NOT LIKE 'pg_%' AND t.relname NOT LIKE 'sql_%' ORDER BY n.nspname, t.relname, c.conname;

-- TABLES and FKEYS
SELECT n.nspname as schema, t.relname as table, c.conname as fk_name FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f') WHERE relkind = 'r' AND t.relname NOT LIKE 'pg_%' AND t.relname NOT LIKE 'sql_%' ORDER BY n.nspname, t.relname, c.conname;

Melvin DavidsonI reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

...

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.

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.*

*https://www.postgresql.org/docs/9.6/static/catalogs.html
<https://www.postgresql.org/docs/9.6/static/catalogs.html&gt;*

*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.