Postgres: list of constraints along with their owners

 

Here is a PostgreSQL SQL query to retrieve a list of constraints along with their owners:

SELECT 
    conname AS constraint_name,
    conrelid::regclass AS table_name,
    pg_catalog.pg_get_userbyid(pg_class.relowner) AS owner
FROM 
    pg_constraint
JOIN 
    pg_class ON conrelid = pg_class.oid
WHERE 
    contype IN ('p', 'u', 'f', 'c') -- p: primary key, u: unique, f: foreign key, c: check
ORDER BY 
    table_name, constraint_name;

Explanation:

1. pg_constraint: Contains information about constraints.
2. pg_class: Provides details about tables and their owners.
3. pg_get_userbyid(pg_class.relowner): Fetches the owner of the table associated with the constraint.
4. contype: Filters constraints by type:
   • p: Primary Key
   • u: Unique
   • f: Foreign Key
   • c: Check Constraint

This query will give you a clear list of constraints, the tables they belong to, and their respective owners.