PostgreSQL is a popularly used open-source relational database that allows us to specify a set of rules on the table’s data. These rules can be added to a table using various CONSTRAINTS, such as PRIMARY KEY, FOREIGN KEY, UNIQUE, etc. Using these constraints users can insert the restricted data into tables. Therefore, for better data manipulation, users are required to get the list of available table constraints. To do that, various approaches are used in Postgres.

This blog post will explain how to list table constraints in Postgres using:

- \d Command
- pg_catalog

So, let’s get started with the “\d” command.

How to Find Table Constraints in Postgres Using \d Command?

“\d” is a metadata command that must be executed from the SQL Shell. It helps us describe the table’s structure including the column names, data types, constraints, and so on. The stated command has a very simple syntax as depicted in the following snippet:

\d tab_name;

From the syntax, you can observe that the stated command must be followed by the table’s name.

Example: Finding Table Constraints Using the “\d” Command

Use the “\d” command along with the table name to list the table constraints:

\d employee_information;

The output shows that the “employee_information” table has a primary key named “employee_info_pkey”.

How to Find Table Constraints in Postgres Using pg_catalog?

In PostgreSQL, the “pg_catalog” schema can be used with the “pg_constraint” and “pg_class” views to get the list of table constraints. More specifically, join both views and utilize the “relname” column to specify the name of the selected table:

SELECT conname AS constraint_name, 
contype AS constraint_type
FROM pg_catalog.pg_constraint cons
JOIN pg_catalog.pg_class t ON t.oid = cons.conrelid
WHERE t.relname ='employee_information';

The constraint type will be denoted by their initials, such as “p” representing the primary key constraint, “c” representing the check constraint, “f” denoting the foreign key constraint, and so on.

The output proves that the pg_catalog.pg_constraint successfully retrieves the constraint name and its respective type.

Conclusion

In PostgreSQL, the “\d” command and “pg_catalog” schema are used to get the list of table constraints. The “\d” command must be used along with the table name to list the table constraints. While the “pg_catalog” schema can be used with the “pg_constraint” and “pg_class” views to get the list of table constraints. This blog has explained a couple of methods to list the table constraints in PostgreSQL.