Thread: Query which shows FK child columns?
v9.6.16 I have a query which shows the parents and children in FK relations, along with the parent column name, but can't seem to find the child column names. Is there a way to find the child column names without having to dig into pg_constraint? Thanks test=# select ccu.table_schema||'.'||ccu.table_name as parent_table, test-# ccu.column_name as parent_column, test-# tc.table_schema||'.'||tc.table_name as child_table, test-# ccu.constraint_schema||'.'||ccu.constraint_name as con_name test-# from information_schema.table_constraints tc, test-# information_schema.constraint_column_usage ccu test-# where tc.constraint_type = 'FOREIGN KEY' test-# and tc.constraint_schema = ccu.constraint_schema test-# and tc.constraint_name = ccu.constraint_name test-# order by parent_table, child_table, ccu.column_name test-# ; parent_table | parent_column | child_table | con_name ---------------------+---------------+---------------------+------------------------------------ public.inventory | inv_id | public.sales_detail | public.sales_detail_sd_inv_id_fkey public.sales_header | parent_id | public.sales_detail | public.sales_detail_id_fkey (2 rows) test=# \d inventory Table "public.inventory" Column | Type | Modifiers -------------+------------------------+----------- inv_id | integer | not null description | character varying(255) | Indexes: "inventory_pkey" PRIMARY KEY, btree (inv_id) Referenced by: TABLE "sales_detail" CONSTRAINT "sales_detail_sd_inv_id_fkey" FOREIGN KEY (sd_inv_id) REFERENCES inventory(inv_id) test=# \d sales_header Table "public.sales_header" Column | Type | Modifiers -----------+---------+----------- parent_id | integer | not null Indexes: "sales_header_pkey" PRIMARY KEY, btree (parent_id) Referenced by: TABLE "sales_detail" CONSTRAINT "sales_detail_id_fkey" FOREIGN KEY (child_id) REFERENCES sales_header(parent_id) test=# \d sales_detail Table "public.sales_detail" Column | Type | Modifiers -----------+---------+----------- child_id | integer | not null seq | integer | not null sd_inv_id | integer | Indexes: "sales_detail_pkey" PRIMARY KEY, btree (child_id, seq) Foreign-key constraints: "sales_detail_id_fkey" FOREIGN KEY (child_id) REFERENCES sales_header(parent_id) "sales_detail_sd_inv_id_fkey" FOREIGN KEY (sd_inv_id) REFERENCES inventory(inv_id) -- Angular momentum makes the world go 'round.
On 11/14/19 11:49 AM, Ron wrote:
v9.6.16
I have a query which shows the parents and children in FK relations, along with the parent column name, but can't seem to find the child column names.
Is there a way to find the child column names without having to dig into pg_constraint?
Thanks
I do not think you can do this without using pg_constraint.
I've been using this function to display those FKs. The original code isn't mine but as I recall I had to tweak it a little.
This is on 10 and I can't remember if this was used on 9.6 but I'd be surprised if any of this won't work on 9.6.
client@cargotel_dev> \sf cargotel_common.show_foreign_keys(text)
CREATE OR REPLACE FUNCTION cargotel_common.show_foreign_keys(tablename text)
RETURNS TABLE(table1 text, column1 text, type text, table2 text, column2 text)
LANGUAGE plpgsql
AS $function$
declare
schemaname text;
begin
select into schemaname current_schema();
return query
execute format('
select
conrelid::regclass::text as table1,
a.attname::text as column1,
t.typname::text as type,
confrelid::regclass::text as table2,
af.attname::text as column2
from
pg_attribute af,
pg_attribute a,
pg_type t,
(
select
conrelid,
confrelid,
conkey[i] as conkey,
confkey[i] as confkey
from (
select
conrelid,
confrelid,
conkey,
confkey,
generate_series(1,array_upper(conkey,1)) as i
from
pg_constraint
where contype = ''f''
)
ss) ss2
where
af.attnum = confkey and
af.attrelid = confrelid and
a.attnum = conkey and
a.attrelid = conrelid and
a.atttypid = t.oid and
confrelid::regclass = ''%I.%I''::regclass
order by 1,2;',schemaname,tablename);
end;
$function$
I use column headings "table 1, column1, table2, column2" but It's easy enough to tweak the column labels.
Example:
client@cargotel_dev> \d+ ref_acct_cache
Table "client.ref_acct_cache"
Column │ Type │ Collation │ Nullable │ Default │ Storage │ Stats target │ Description
────────┼─────────┼───────────┼──────────┼────────────────────────────────────────────┼──────────┼──────────────┼─────────────
id │ integer │ │ not null │ nextval('ref_acct_cache_id_seq'::regclass) │ plain │ │
descr │ text │ │ │ │ extended │ │
Indexes:
"ref_acct_cache_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "acct_cache" CONSTRAINT "acct_cache_type_id_ref_acct_cache_id_fk" FOREIGN KEY (type_id) REFERENCES ref_acct_cache(id)
client@cargotel_dev> select * from cargotel_common.show_foreign_keys('ref_acct_cache');
table1 │ column1 │ type │ table2 │ column2
────────────┼─────────┼──────┼────────────────┼─────────
acct_cache │ type_id │ int4 │ ref_acct_cache │ id
(1 row)
client@cargotel_dev> \d+ acct_cache
Table "client.acct_cache"
Column │ Type │ Collation │ Nullable │ Default │ Storage │ Stats target │ Description
───────────────┼──────────────────────────┼───────────┼──────────┼────────────────────────────────────────┼──────────┼──────────────┼─────────────
id │ integer │ │ not null │ nextval('acct_cache_id_seq'::regclass) │ plain │ │
type_id │ integer │ │ │ │ plain │ │
prefix │ text │ │ │ │ extended │ │
data │ text │ │ │ │ extended │ │
amount │ numeric │ │ │ │ main │ │
timestamp │ timestamp with time zone │ │ │ │ plain │ │
check_number │ text │ │ │ │ extended │ │
client_number │ text │ │ │ │ extended │ │
check_date │ date │ │ │ │ plain │ │
Indexes:
"acct_cache_pkey" PRIMARY KEY, btree (id)
"acct_cache_prefix_type_id_data_idx" btree (prefix, type_id, data)
"acct_cache_type_id_idx" btree (type_id)
Foreign-key constraints:
"acct_cache_type_id_ref_acct_cache_id_fk" FOREIGN KEY (type_id) REFERENCES ref_acct_cache(id)
Referenced by:
TABLE "load_trx" CONSTRAINT "load_trx_ar_voucher_id_acct_cache_id_fk" FOREIGN KEY (ar_voucher_id) REFERENCES acct_cache(id)
TABLE "loadacct_link" CONSTRAINT "loadacct_link_acct_cache_id_acct_cache_id_fk" FOREIGN KEY (acct_cache_id) REFERENCES acct_cache(id)
TABLE "qb_invoice_incomplete" CONSTRAINT "qb_invoice_incomplete_acct_cache_id_acct_cache_id_fk" FOREIGN KEY (acct_cache_id) REFERENCES acct_cache(id)
TABLE "qb_payment_log" CONSTRAINT "qb_payment_log_acct_cache_id_acct_cache_id_fk" FOREIGN KEY (acct_cache_id) REFERENCES acct_cache(id)
And as a bonus:
client@cargotel_dev> select * from cargotel_common.show_foreign_keys('acct_cache');
table1 │ column1 │ type │ table2 │ column2
───────────────────────┼───────────────┼──────┼────────────┼─────────
loadacct_link │ acct_cache_id │ int4 │ acct_cache │ id
load_trx │ ar_voucher_id │ int4 │ acct_cache │ id
qb_invoice_incomplete │ acct_cache_id │ int4 │ acct_cache │ id
qb_payment_log │ acct_cache_id │ int4 │ acct_cache │ id
(4 rows)
Hope that helps!
Jeff
Jeff Ross <jross@openvistas.net> writes:
> On 11/14/19 11:49 AM, Ron wrote:
>> I have a query which shows the parents and children in FK relations,
>> along with the parent column name, but can't seem to find the child
>> column names.
>> Is there a way to find the child column names without having to dig
>> into pg_constraint?
> I do not think you can do this without using pg_constraint.
In principle, you can get useful information out of a join
of information_schema.referential_constraints and
information_schema.key_column_usage, but I think the only appeal
that would have is (theoretical) portability to other DBMSes.
It'd likely be horribly slow in any nontrivial database, because
the information_schema views really don't map very well onto
the Postgres catalogs, so the view definitions are overcomplicated
already ... and then you gotta join them to get what you want.
Aside from manual queries of pg_constraint, you might find it
useful to do what psql and pg_dump do, namely use one of the
built-in functions that reconstruct the text form of some SQL
entity. In this case pg_catalog.pg_get_constraintdef(oid)
might serve.
For example, in a database containing only
d1=# create table pk(a int, b int, primary key(a,b));
CREATE TABLE
d1=# create table fk(x int, y int, foreign key (x,y) references pk);
CREATE TABLE
I get
d1=# table information_schema.referential_constraints;
constraint_catalog | constraint_schema | constraint_name | unique_constraint_catalog | unique_constraint_schema |
unique_constraint_name| match_option | update_rule | delete_rule
--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------
d1 | public | fk_x_y_fkey | d1 | public |
pk_pkey | NONE | NO ACTION | NO ACTION
(1 row)
d1=# table information_schema.key_column_usage;
constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | column_name |
ordinal_position| position_in_unique_constraint
--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------
d1 | public | pk_pkey | d1 | public | pk | a |
1 |
d1 | public | pk_pkey | d1 | public | pk | b |
2 |
d1 | public | fk_x_y_fkey | d1 | public | fk | x |
1 | 1
d1 | public | fk_x_y_fkey | d1 | public | fk | y |
2 | 2
(4 rows)
so something could be made out of that, but not without some work to
link up the FK and unique constraints. Meanwhile
d1=# select conname, conrelid::regclass, pg_catalog.pg_get_constraintdef(oid) from pg_constraint;
conname | conrelid | pg_get_constraintdef
------------------------------+----------+----------------------------------------------------------------------------------------------------
cardinal_number_domain_check | - | CHECK ((VALUE >= 0))
yes_or_no_check | - | CHECK (((VALUE)::text = ANY ((ARRAY['YES'::character varying,
'NO'::charactervarying])::text[])))
pk_pkey | pk | PRIMARY KEY (a, b)
fk_x_y_fkey | fk | FOREIGN KEY (x, y) REFERENCES pk(a, b)
(4 rows)
(Those first two CHECK constraints seem to belong to domains defined in
the information_schema itself.)
Of course, if what you need is something that can be programmatically
analyzed, these text forms aren't too helpful --- but if you just
want to see what the constraints are, then this is a good way.
regards, tom lane