27

How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?

28

Constraints can be retrieved via pg_catalog.pg_constraint.

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';

Replace <schema name> with the name of your schema and <table name> with the name of your table.

  • 1
    Notice that pg_catalog.pg_constraint does not contain NOT NULL constraints. – Luís de Sousa Jul 24 at 8:30
4

In the psql command line this information is in the table sheet, obtained with the \d+ command. d+ also informs on the NOT NULL constraints, something that is not present in the pg_catalog.pg_constraint table. An example:

# \d+ observations.stream   
                                                  Table "observations.stream"
 Column |       Type        | Collation | Nullable | Default | Storage  | Stats target |                 Description                 
--------+-------------------+-----------+----------+---------+----------+--------------+---------------------------------------------
 id     | integer           |           | not null |         | plain    |              | 
 name   | character varying |           | not null |         | extended |              | This should be a table in the import schema
 min_id | integer           |           | not null |         | plain    |              | 
 about  | character varying |           | not null |         | extended |              | 
Indexes:
    "stream_pkey" PRIMARY KEY, btree (id)
    "stream_name_key" UNIQUE CONSTRAINT, btree (name)
Check constraints:
    "stream_id_check" CHECK (id > 0)
Referenced by:
    TABLE "profile" CONSTRAINT "profile_id_stream_fkey" FOREIGN KEY (id_stream) REFERENCES stream(id)

The caveat here is that you do not get the names of all the constraints this way.

  • I see constraint names in your posted output. – ypercubeᵀᴹ Jul 24 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.