Home » Programming » Databases » How to Find Dependent Objects in a PostgreSQL Database

How to Find Dependent Objects in a PostgreSQL Database

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:

    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
    pg_depend pgd
    JOIN pg_class pgc ON pgc.oid = pgd.objid
    JOIN pg_class pgt ON pgt.oid = pgd.refobjid
    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.

Photo of author
I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Comment