Re: Query which shows FK child columns?

Jeff Ross <jross(at)openvistas(dot)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'::character varying])::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