Re: [GENERAL] Queries for Diagramming Schema Keys - Mailing list pgsql-general
...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.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;
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;
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;
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: