This article will show you how to find dependent objects in a PostgreSQL database. This is useful when deleting tables, functions, columns, and other PostgreSQL objects, allowing you to make sure that no other database objects reference them.
Finding dependent objects is especially important if you are deleting a column, table, function, procedure, or other object using the CASCADE option, so that you can check in advance what may be deleted by the operation.
Using the pg_depend Catalog to find Dependent Objects
The pg_depend catalog contains a record of all object relationships in a PostgreSQL database. This catalog is used by DROP commands to find and delete related objects, and can be used to check what will be deleted before doing so.
You can view a list of all PostgreSQL object dependencies from pg_depend by running:
SELECT * FROM pg_depend;
The resulting output includes the names, schemas, object IDs and types of the depended and dependent objects. This list is, however, not very useful unless you wish to manually go through and check for dependencies.
It is best instead to construct a query that will extract this information for you. For example, the below query will output the dependencies between your tables:
SELECT
pgc.oid AS table_oid,
pgc.relname AS table_name,
pgd.refobjid AS dependent_oid,
pgd.refobjsubid AS dependent_column,
pgt.relname AS dependent_table
FROM
pg_depend pgd
JOIN pg_class pgc ON pgc.oid = pgd.objid
JOIN pg_class pgt ON pgt.oid = pgd.refobjid
WHERE
pgc.relkind = 'r'
AND pgt.relkind = 'r';
The resulting output will display all of the tables and the names of the tables that depend on them.
Based on the output returned by listing all object dependencies, you can tailor the above query to reference specific tables, columns, or other objects to gain a filtered list of dependencies, limiting the results to only those you wish to check.
