How to Find the Name of a Constraint in PostgreSQL

Problem

You want to find the names of the constraints in a table in PostgreSQL.

Example

We want to display the names of the constraints in the table student.

Solution

SELECT conname, contype
FROM pg_catalog.pg_constraint
JOIN pg_class t ON t.oid = c.conrelid
WHERE t.relname ='student';

Here is the result:

Discussion

To find the name of a constraint in PostgreSQL, use the view pg_constraint in the pg_catalog schema. Join the view pg_catalog.pg_constraint with the view pg_class (JOIN pg_class t ON t.oid = c.conrelid) and use the relname column to filter the constraints by table name. In our example, we select the constraints from the table student (WHERE t.relname ='student').

Select columns conname and contype to see the constraint name along with the constraint type. In the column contype, the value ‘p’ is for a primary key, ‘f’ is for a foreign key, ‘u’ is for the UNIQUE constraint, and ‘c’ is for the CHECK constraint.

For example, you can see the constraint named student_pkey for the primary key in the student table. The contype column tells you the type of the constraint, which for the primary key is p. The conname column in this view also tells you about the table containing this constraint.