Back to cookbooks list Articles Cookbook

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:

connamecontype
student_age_checkc
student_pkeyp
student_personal_number_keyu
student_country_id_fkeyf

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.

Recommended courses:

Recommended articles:

See also: