Re: [GENERAL] Queries for Diagramming Schema Keys

[Melvin Davidson]

On Mon, Aug 14, 2017 at 6:38 PM,  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
*


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

Re: [GENERAL] Queries for Diagramming Schema Keys

[stimits]

...

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

Re: [GENERAL] Queries for Diagramming Schema Keys

[stimits]

...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_typeEND,   
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. 

Re: [GENERAL] Queries for Diagramming Schema Keys

[Scott Marlowe]

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.

Re: [GENERAL] Queries for Diagramming Schema Keys

[Melvin Davidson]

On Mon, Aug 14, 2017 at 2:46 PM,  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 

[GENERAL] Queries for Diagramming Schema Keys

[stimits]

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.tablesWHERE 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:
SELECTtc.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_nameJOIN information_schema.constraint_column_usage AS ccu   
   ON ccu.constraint_name = tc.constraint_nameWHERE constraint_type = 'FOREIGN 
KEY' AND tc.table_nameIN (SELECT table_name  FROM 
information_schema.tablesWHERE 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 DISTINCTtc.table_name AS local_table,kcu.column_name AS 
key_columnFROM information_schema.table_constraints AS tc JOIN 
information_schema.key_column_usage AS kcu  ON tc.constraint_name = 
kcu.constraint_nameJOIN information_schema.constraint_column_usage AS ccu   
   ON ccu.constraint_name = tc.constraint_nameWHERE constraint_type = 'PRIMARY 
KEY' AND tc.table_nameIN (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 (SELECTtc.table_name,kcu.column_nameFROM
information_schema.table_constraints AS tc JOIN 
information_schema.key_column_usage AS kcu  ON tc.constraint_name = 
kcu.constraint_nameJOIN information_schema.constraint_column_usage AS ccu   
   ON ccu.constraint_name = tc.constraint_nameWHERE constraint_type = 'FOREIGN 
KEY' AND tc.table_nameIN (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!