• Browse categories
• PostgreSQL releases
  • PostgreSQL 15 (beta1)
  • PostgreSQL 14 (latest)
  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10
  • PostgreSQL 9.6 (EOL)

Version charts

Useful overviews

Useful links

pg_constraint

pg_constraint is a system catalogue table used to store information about constraints (excluding NOT NULL constraints).

pg_constraint was added in PostgreSQL 7.3.

Usage

System functions

The system function pg_get_constraintdef() can be used to obtain the definition of a constraint.

Implementation and possible changes

NOT NULL constraints

Currently NOT NULL constraints are stored in pg_attribute; a commit in commit e49ae8d3 (from 2010) implies there is or was an intent to move them to this table.

Domain constraints

Commit 8abb3cda (from 2015) includes a suggestion to add a separate table for domain constraints.

Definition by PostgreSQL version

                Table "pg_catalog.pg_constraint"
     Column     |     Type     | Collation | Nullable | Default 
----------------+--------------+-----------+----------+---------
 oid            | oid          |           | not null | 
 conname        | name         |           | not null | 
 connamespace   | oid          |           | not null | 
 contype        | "char"       |           | not null | 
 condeferrable  | boolean      |           | not null | 
 condeferred    | boolean      |           | not null | 
 convalidated   | boolean      |           | not null | 
 conrelid       | oid          |           | not null | 
 contypid       | oid          |           | not null | 
 conindid       | oid          |           | not null | 
 conparentid    | oid          |           | not null | 
 confrelid      | oid          |           | not null | 
 confupdtype    | "char"       |           | not null | 
 confdeltype    | "char"       |           | not null | 
 confmatchtype  | "char"       |           | not null | 
 conislocal     | boolean      |           | not null | 
 coninhcount    | integer      |           | not null | 
 connoinherit   | boolean      |           | not null | 
 conkey         | smallint[]   |           |          | 
 confkey        | smallint[]   |           |          | 
 conpfeqop      | oid[]        |           |          | 
 conppeqop      | oid[]        |           |          | 
 conffeqop      | oid[]        |           |          | 
 confdelsetcols | smallint[]   |           |          | 
 conexclop      | oid[]        |           |          | 
 conbin         | pg_node_tree | C         |          | 
Indexes:
    "pg_constraint_oid_index" PRIMARY KEY, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conparentid_index" btree (conparentid)
    "pg_constraint_conrelid_contypid_conname_index" UNIQUE CONSTRAINT, btree (conrelid, contypid, conname)
    "pg_constraint_contypid_index" btree (contypid)
    

               Table "pg_catalog.pg_constraint"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 oid           | oid          |           | not null | 
 conname       | name         |           | not null | 
 connamespace  | oid          |           | not null | 
 contype       | "char"       |           | not null | 
 condeferrable | boolean      |           | not null | 
 condeferred   | boolean      |           | not null | 
 convalidated  | boolean      |           | not null | 
 conrelid      | oid          |           | not null | 
 contypid      | oid          |           | not null | 
 conindid      | oid          |           | not null | 
 conparentid   | oid          |           | not null | 
 confrelid     | oid          |           | not null | 
 confupdtype   | "char"       |           | not null | 
 confdeltype   | "char"       |           | not null | 
 confmatchtype | "char"       |           | not null | 
 conislocal    | boolean      |           | not null | 
 coninhcount   | integer      |           | not null | 
 connoinherit  | boolean      |           | not null | 
 conkey        | smallint[]   |           |          | 
 confkey       | smallint[]   |           |          | 
 conpfeqop     | oid[]        |           |          | 
 conppeqop     | oid[]        |           |          | 
 conffeqop     | oid[]        |           |          | 
 conexclop     | oid[]        |           |          | 
 conbin        | pg_node_tree | C         |          | 
Indexes:
    "pg_constraint_oid_index" PRIMARY KEY, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conparentid_index" btree (conparentid)
    "pg_constraint_conrelid_contypid_conname_index" UNIQUE CONSTRAINT, btree (conrelid, contypid, conname)
    "pg_constraint_contypid_index" btree (contypid)
    

               Table "pg_catalog.pg_constraint"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 oid           | oid          |           | not null | 
 conname       | name         |           | not null | 
 connamespace  | oid          |           | not null | 
 contype       | "char"       |           | not null | 
 condeferrable | boolean      |           | not null | 
 condeferred   | boolean      |           | not null | 
 convalidated  | boolean      |           | not null | 
 conrelid      | oid          |           | not null | 
 contypid      | oid          |           | not null | 
 conindid      | oid          |           | not null | 
 conparentid   | oid          |           | not null | 
 confrelid     | oid          |           | not null | 
 confupdtype   | "char"       |           | not null | 
 confdeltype   | "char"       |           | not null | 
 confmatchtype | "char"       |           | not null | 
 conislocal    | boolean      |           | not null | 
 coninhcount   | integer      |           | not null | 
 connoinherit  | boolean      |           | not null | 
 conkey        | smallint[]   |           |          | 
 confkey       | smallint[]   |           |          | 
 conpfeqop     | oid[]        |           |          | 
 conppeqop     | oid[]        |           |          | 
 conffeqop     | oid[]        |           |          | 
 conexclop     | oid[]        |           |          | 
 conbin        | pg_node_tree | C         |          | 
Indexes:
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conparentid_index" btree (conparentid)
    "pg_constraint_conrelid_contypid_conname_index" UNIQUE, btree (conrelid, contypid, conname)
    "pg_constraint_contypid_index" btree (contypid)
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    

               Table "pg_catalog.pg_constraint"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 oid           | oid          |           | not null | 
 conname       | name         |           | not null | 
 connamespace  | oid          |           | not null | 
 contype       | "char"       |           | not null | 
 condeferrable | boolean      |           | not null | 
 condeferred   | boolean      |           | not null | 
 convalidated  | boolean      |           | not null | 
 conrelid      | oid          |           | not null | 
 contypid      | oid          |           | not null | 
 conindid      | oid          |           | not null | 
 conparentid   | oid          |           | not null | 
 confrelid     | oid          |           | not null | 
 confupdtype   | "char"       |           | not null | 
 confdeltype   | "char"       |           | not null | 
 confmatchtype | "char"       |           | not null | 
 conislocal    | boolean      |           | not null | 
 coninhcount   | integer      |           | not null | 
 connoinherit  | boolean      |           | not null | 
 conkey        | smallint[]   |           |          | 
 confkey       | smallint[]   |           |          | 
 conpfeqop     | oid[]        |           |          | 
 conppeqop     | oid[]        |           |          | 
 conffeqop     | oid[]        |           |          | 
 conexclop     | oid[]        |           |          | 
 conbin        | pg_node_tree | C         |          | 
Indexes:
    "pg_constraint_conrelid_contypid_conname_index" UNIQUE, btree (conrelid, contypid, conname)
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conparentid_index" btree (conparentid)
    "pg_constraint_contypid_index" btree (contypid)
    

               Table "pg_catalog.pg_constraint"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 conname       | name         |           | not null | 
 connamespace  | oid          |           | not null | 
 contype       | "char"       |           | not null | 
 condeferrable | boolean      |           | not null | 
 condeferred   | boolean      |           | not null | 
 convalidated  | boolean      |           | not null | 
 conrelid      | oid          |           | not null | 
 contypid      | oid          |           | not null | 
 conindid      | oid          |           | not null | 
 conparentid   | oid          |           | not null | 
 confrelid     | oid          |           | not null | 
 confupdtype   | "char"       |           | not null | 
 confdeltype   | "char"       |           | not null | 
 confmatchtype | "char"       |           | not null | 
 conislocal    | boolean      |           | not null | 
 coninhcount   | integer      |           | not null | 
 connoinherit  | boolean      |           | not null | 
 conkey        | smallint[]   |           |          | 
 confkey       | smallint[]   |           |          | 
 conpfeqop     | oid[]        |           |          | 
 conppeqop     | oid[]        |           |          | 
 conffeqop     | oid[]        |           |          | 
 conexclop     | oid[]        |           |          | 
 conbin        | pg_node_tree |           |          | 
 consrc        | text         |           |          | 
Indexes:
    "pg_constraint_conrelid_contypid_conname_index" UNIQUE, btree (conrelid, contypid, conname)
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conparentid_index" btree (conparentid)
    "pg_constraint_contypid_index" btree (contypid)
    

               Table "pg_catalog.pg_constraint"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 conname       | name         |           | not null | 
 connamespace  | oid          |           | not null | 
 contype       | "char"       |           | not null | 
 condeferrable | boolean      |           | not null | 
 condeferred   | boolean      |           | not null | 
 convalidated  | boolean      |           | not null | 
 conrelid      | oid          |           | not null | 
 contypid      | oid          |           | not null | 
 conindid      | oid          |           | not null | 
 confrelid     | oid          |           | not null | 
 confupdtype   | "char"       |           | not null | 
 confdeltype   | "char"       |           | not null | 
 confmatchtype | "char"       |           | not null | 
 conislocal    | boolean      |           | not null | 
 coninhcount   | integer      |           | not null | 
 connoinherit  | boolean      |           | not null | 
 conkey        | smallint[]   |           |          | 
 confkey       | smallint[]   |           |          | 
 conpfeqop     | oid[]        |           |          | 
 conppeqop     | oid[]        |           |          | 
 conffeqop     | oid[]        |           |          | 
 conexclop     | oid[]        |           |          | 
 conbin        | pg_node_tree |           |          | 
 consrc        | text         |           |          | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

     Table "pg_catalog.pg_constraint"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 conname       | name         | not null
 connamespace  | oid          | not null
 contype       | "char"       | not null
 condeferrable | boolean      | not null
 condeferred   | boolean      | not null
 convalidated  | boolean      | not null
 conrelid      | oid          | not null
 contypid      | oid          | not null
 conindid      | oid          | not null
 confrelid     | oid          | not null
 confupdtype   | "char"       | not null
 confdeltype   | "char"       | not null
 confmatchtype | "char"       | not null
 conislocal    | boolean      | not null
 coninhcount   | integer      | not null
 connoinherit  | boolean      | not null
 conkey        | smallint[]   | 
 confkey       | smallint[]   | 
 conpfeqop     | oid[]        | 
 conppeqop     | oid[]        | 
 conffeqop     | oid[]        | 
 conexclop     | oid[]        | 
 conbin        | pg_node_tree | 
 consrc        | text         | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

     Table "pg_catalog.pg_constraint"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 conname       | name         | not null
 connamespace  | oid          | not null
 contype       | "char"       | not null
 condeferrable | boolean      | not null
 condeferred   | boolean      | not null
 convalidated  | boolean      | not null
 conrelid      | oid          | not null
 contypid      | oid          | not null
 conindid      | oid          | not null
 confrelid     | oid          | not null
 confupdtype   | "char"       | not null
 confdeltype   | "char"       | not null
 confmatchtype | "char"       | not null
 conislocal    | boolean      | not null
 coninhcount   | integer      | not null
 connoinherit  | boolean      | not null
 conkey        | smallint[]   | 
 confkey       | smallint[]   | 
 conpfeqop     | oid[]        | 
 conppeqop     | oid[]        | 
 conffeqop     | oid[]        | 
 conexclop     | oid[]        | 
 conbin        | pg_node_tree | 
 consrc        | text         | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

     Table "pg_catalog.pg_constraint"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 conname       | name         | not null
 connamespace  | oid          | not null
 contype       | "char"       | not null
 condeferrable | boolean      | not null
 condeferred   | boolean      | not null
 convalidated  | boolean      | not null
 conrelid      | oid          | not null
 contypid      | oid          | not null
 conindid      | oid          | not null
 confrelid     | oid          | not null
 confupdtype   | "char"       | not null
 confdeltype   | "char"       | not null
 confmatchtype | "char"       | not null
 conislocal    | boolean      | not null
 coninhcount   | integer      | not null
 connoinherit  | boolean      | not null
 conkey        | smallint[]   | 
 confkey       | smallint[]   | 
 conpfeqop     | oid[]        | 
 conppeqop     | oid[]        | 
 conffeqop     | oid[]        | 
 conexclop     | oid[]        | 
 conbin        | pg_node_tree | 
 consrc        | text         | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

     Table "pg_catalog.pg_constraint"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 conname       | name         | not null
 connamespace  | oid          | not null
 contype       | "char"       | not null
 condeferrable | boolean      | not null
 condeferred   | boolean      | not null
 convalidated  | boolean      | not null
 conrelid      | oid          | not null
 contypid      | oid          | not null
 conindid      | oid          | not null
 confrelid     | oid          | not null
 confupdtype   | "char"       | not null
 confdeltype   | "char"       | not null
 confmatchtype | "char"       | not null
 conislocal    | boolean      | not null
 coninhcount   | integer      | not null
 connoinherit  | boolean      | not null
 conkey        | smallint[]   | 
 confkey       | smallint[]   | 
 conpfeqop     | oid[]        | 
 conppeqop     | oid[]        | 
 conffeqop     | oid[]        | 
 conexclop     | oid[]        | 
 conbin        | pg_node_tree | 
 consrc        | text         | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

     Table "pg_catalog.pg_constraint"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 conname       | name         | not null
 connamespace  | oid          | not null
 contype       | "char"       | not null
 condeferrable | boolean      | not null
 condeferred   | boolean      | not null
 convalidated  | boolean      | not null
 conrelid      | oid          | not null
 contypid      | oid          | not null
 conindid      | oid          | not null
 confrelid     | oid          | not null
 confupdtype   | "char"       | not null
 confdeltype   | "char"       | not null
 confmatchtype | "char"       | not null
 conislocal    | boolean      | not null
 coninhcount   | integer      | not null
 connoinherit  | boolean      | not null
 conkey        | smallint[]   | 
 confkey       | smallint[]   | 
 conpfeqop     | oid[]        | 
 conppeqop     | oid[]        | 
 conffeqop     | oid[]        | 
 conexclop     | oid[]        | 
 conbin        | pg_node_tree | 
 consrc        | text         | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

     Table "pg_catalog.pg_constraint"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 conname       | name         | not null
 connamespace  | oid          | not null
 contype       | "char"       | not null
 condeferrable | boolean      | not null
 condeferred   | boolean      | not null
 convalidated  | boolean      | not null
 conrelid      | oid          | not null
 contypid      | oid          | not null
 conindid      | oid          | not null
 confrelid     | oid          | not null
 confupdtype   | "char"       | not null
 confdeltype   | "char"       | not null
 confmatchtype | "char"       | not null
 conislocal    | boolean      | not null
 coninhcount   | integer      | not null
 conkey        | smallint[]   | 
 confkey       | smallint[]   | 
 conpfeqop     | oid[]        | 
 conppeqop     | oid[]        | 
 conffeqop     | oid[]        | 
 conexclop     | oid[]        | 
 conbin        | pg_node_tree | 
 consrc        | text         | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

    Table "pg_catalog.pg_constraint"
    Column     |    Type    | Modifiers 
---------------+------------+-----------
 conname       | name       | not null
 connamespace  | oid        | not null
 contype       | "char"     | not null
 condeferrable | boolean    | not null
 condeferred   | boolean    | not null
 conrelid      | oid        | not null
 contypid      | oid        | not null
 conindid      | oid        | not null
 confrelid     | oid        | not null
 confupdtype   | "char"     | not null
 confdeltype   | "char"     | not null
 confmatchtype | "char"     | not null
 conislocal    | boolean    | not null
 coninhcount   | integer    | not null
 conkey        | smallint[] | 
 confkey       | smallint[] | 
 conpfeqop     | oid[]      | 
 conppeqop     | oid[]      | 
 conffeqop     | oid[]      | 
 conexclop     | oid[]      | 
 conbin        | text       | 
 consrc        | text       | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

    Table "pg_catalog.pg_constraint"
    Column     |    Type    | Modifiers 
---------------+------------+-----------
 conname       | name       | not null
 connamespace  | oid        | not null
 contype       | "char"     | not null
 condeferrable | boolean    | not null
 condeferred   | boolean    | not null
 conrelid      | oid        | not null
 contypid      | oid        | not null
 confrelid     | oid        | not null
 confupdtype   | "char"     | not null
 confdeltype   | "char"     | not null
 confmatchtype | "char"     | not null
 conislocal    | boolean    | not null
 coninhcount   | integer    | not null
 conkey        | smallint[] | 
 confkey       | smallint[] | 
 conpfeqop     | oid[]      | 
 conppeqop     | oid[]      | 
 conffeqop     | oid[]      | 
 conbin        | text       | 
 consrc        | text       | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

    Table "pg_catalog.pg_constraint"
    Column     |    Type    | Modifiers 
---------------+------------+-----------
 conname       | name       | not null
 connamespace  | oid        | not null
 contype       | "char"     | not null
 condeferrable | boolean    | not null
 condeferred   | boolean    | not null
 conrelid      | oid        | not null
 contypid      | oid        | not null
 confrelid     | oid        | not null
 confupdtype   | "char"     | not null
 confdeltype   | "char"     | not null
 confmatchtype | "char"     | not null
 conkey        | smallint[] | 
 confkey       | smallint[] | 
 conpfeqop     | oid[]      | 
 conppeqop     | oid[]      | 
 conffeqop     | oid[]      | 
 conbin        | text       | 
 consrc        | text       | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

    Table "pg_catalog.pg_constraint"
    Column     |    Type    | Modifiers 
---------------+------------+-----------
 conname       | name       | not null
 connamespace  | oid        | not null
 contype       | "char"     | not null
 condeferrable | boolean    | not null
 condeferred   | boolean    | not null
 conrelid      | oid        | not null
 contypid      | oid        | not null
 confrelid     | oid        | not null
 confupdtype   | "char"     | not null
 confdeltype   | "char"     | not null
 confmatchtype | "char"     | not null
 conkey        | smallint[] | 
 confkey       | smallint[] | 
 conbin        | text       | 
 consrc        | text       | 
Indexes:
    "pg_constraint_oid_index" UNIQUE, btree (oid)
    "pg_constraint_conname_nsp_index" btree (conname, connamespace)
    "pg_constraint_conrelid_index" btree (conrelid)
    "pg_constraint_contypid_index" btree (contypid)
    

Change history

• PostgreSQL 15
  • column confdelsetcols added (commit d6f96ed9)
• PostgreSQL 12
  • column consrc removed (commit 96b00c43)
• PostgreSQL 9.3
  • column confmatchtype: default foreign key match style is now MATCH SIMPLE (commit f5297bdf)
• PostgreSQL 9.2
  • column conisonly renamed to connoinherit (commit 09ff76fc)
• PostgreSQL 9.0
  • column conindid added (commit c1b9ec24)
• PostgreSQL 8.4
  • columns conislocal and coninhcount added (commit cd902b33)
• PostgreSQL 8.3
  • columns conpfeqop, conppeqop and conffeqop added (commit 7bddca34)
• PostgreSQL 7.3
  • added (commit 7c6df91d)

References

Categories

See also