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
pg_constraint (PostgreSQL 15)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 14)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 13)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 12)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 11)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 10)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 9.6)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 9.5)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 9.4)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 9.3)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 9.2)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 9.1)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 9.0)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 8.4)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 8.3)
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)
Documentation: pg_constraint
pg_constraint (PostgreSQL 8.2)
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)
Documentation: pg_constraint
Change history
- PostgreSQL 15
- column
confdelsetcols
added (commit d6f96ed9)
- column
- PostgreSQL 12
- column
consrc
removed (commit 96b00c43)
- column
- PostgreSQL 9.3
- column
confmatchtype
: default foreign key match style is nowMATCH SIMPLE
(commit f5297bdf)
- column
- PostgreSQL 9.2
- column
conisonly
renamed toconnoinherit
(commit 09ff76fc)
- column
- PostgreSQL 9.0
- column
conindid
added (commit c1b9ec24)
- column
- PostgreSQL 8.4
- columns
conislocal
andconinhcount
added (commit cd902b33)
- columns
- PostgreSQL 8.3
- columns
conpfeqop
,conppeqop
andconffeqop
added (commit 7bddca34)
- columns
- PostgreSQL 7.3
- added (commit 7c6df91d)
References
- PostgreSQL documentation: pg_constraint