PostgreSQL: SQL script to get a list of all tables that have a particular column as foreign key

SELECT    r.table_nameFROM information_schema.constraint_column_usage       uINNER JOIN information_schema.referential_constraints fk           ON u.constraint_catalog = fk.unique_constraint_catalog               AND u.constraint_schema = fk.unique_constraint_schema               AND u.constraint_name = fk.unique_constraint_nameINNER JOIN information_schema.key_column_usage        r           ON r.constraint_catalog = fk.constraint_catalog               AND r.constraint_schema = fk.constraint_schema               AND r.constraint_name = fk.constraint_nameWHERE    u.column_name = 'id' AND    u.table_catalog = 'db_name' AND    u.table_schema = 'public' AND    u.table_name = 'table_a'

This uses the full catalog/schema/name triplet to identify a db table from all 3 information_schema views. You can drop one or two as required.

The query lists all tables that have a foreign key constraint against the column 'a' in table 'd'

The other solutions are not guaranteed to work in postgresql, as the constraint_name is not guaranteed to be unique; thus you will get false positives. PostgreSQL used to name constraints silly things like '$1', and if you've got an old database you've been maintaining through upgrades, you likely still have some of those around.

Since this question was targeted AT PostgreSQL and that is what you are using, then you can query the internal postgres tables pg_class and pg_attribute to get a more accurate result.

NOTE: FKs can be on multiple columns, thus the referencing column (attnum of pg_attribute) is an ARRAY, which is the reason for using array_agg in the answer.

The only thing you need plug in is the TARGET_TABLE_NAME:

select   (select r.relname from pg_class r where r.oid = c.conrelid) as table,   (select array_agg(attname) from pg_attribute    where attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as col,   (select r.relname from pg_class r where r.oid = c.confrelid) as ftable from pg_constraint c where c.confrelid = (select oid from pg_class where relname = 'TARGET_TABLE_NAME');

If you want to go the other way (list all of the things a specific table refers to), then just change the last line to:

where c.conrelid = (select oid from pg_class where relname = 'TARGET_TABLE_NAME');

Oh, and since the actual question was to target a specific column, you can specify the column name with this one:

select (select r.relname from pg_class r where r.oid = c.conrelid) as table,        (select array_agg(attname) from pg_attribute         where attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as col,        (select r.relname from pg_class r where r.oid = c.confrelid) as ftable from pg_constraint c where c.confrelid = (select oid from pg_class where relname = 'TARGET_TABLE_NAME') and       c.confkey @> (select array_agg(attnum) from pg_attribute                     where attname = 'TARGET_COLUMN_NAME' and attrelid = c.confrelid);

Personally, I prefer to query based on the referenced unique constraint rather than the column. That would look something like this:

SELECT rc.constraint_catalog,       rc.constraint_schema||'.'||tc.table_name AS table_name,       kcu.column_name,       match_option,       update_rule,       delete_ruleFROM information_schema.referential_constraints AS rc     JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)    JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)WHERE unique_constraint_catalog='catalog'    AND unique_constraint_schema='schema'    AND unique_constraint_name='constraint name';

Here is a version that allows querying by column name:

SELECT rc.constraint_catalog,       rc.constraint_schema||'.'||tc.table_name AS table_name,       kcu.column_name,       match_option,       update_rule,       delete_ruleFROM information_schema.referential_constraints AS rc    JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)    JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)    JOIN information_schema.key_column_usage AS ccu ON(ccu.constraint_catalog=rc.unique_constraint_catalog AND ccu.constraint_schema=rc.unique_constraint_schema AND ccu.constraint_name=rc.unique_constraint_name)WHERE ccu.table_catalog='catalog'    AND ccu.table_schema='schema'    AND ccu.table_name='name'    AND ccu.column_name='column';