SYSTEM CATALOG · System catalog

pg_constraint

PostgreSQL documentation ↗Source definition ↗

The catalog pg_constraint stores check, not-null, primary key, unique, foreign key, and exclusion constraints on tables. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) User-defined constraint triggers (created with CREATE CONSTRAINT TRIGGER) also give rise to an entry in this table. Check constraints on domains are stored here, too.

First seen PG 9.0Last seen PG 19Columns 28

9.0 9.1 9.2 9.3 9.4 9.5 9.6 10 11 12 13 14 15 16 17 18 19^β3

Checked column by column against a PostgreSQL 18.6 instance · Raw measurement record ↗

Columns PostgreSQL 18

pg_constraint

COLUMN	TYPE	DESCRIPTION
`oid`	`oid`NOT NULL	Row identifier
`conname`	`name`NOT NULL	Constraint name (not necessarily unique!)
`connamespace`	`oid`NOT NULL	The OID of the namespace that contains this constraintReferences:`pg_namespace.oid`
`contype`	`"char"`NOT NULLDocumented as:`char`	c = check constraint, f = foreign key constraint, n = not-null constraint, p = primary key constraint, u = unique constraint, t = constraint trigger, x = exclusion constraint
`condeferrable`	`boolean`NOT NULLDocumented as:`bool`	Is the constraint deferrable?
`condeferred`	`boolean`NOT NULLDocumented as:`bool`	Is the constraint deferred by default?
`conenforced`	`boolean`NOT NULLDocumented as:`bool`	Is the constraint enforced?
`convalidated`	`boolean`NOT NULLDocumented as:`bool`	Has the constraint been validated?
`conrelid`	`oid`NOT NULL	The table this constraint is on; zero if not a table constraintReferences:`pg_class.oid`
`contypid`	`oid`NOT NULL	The domain this constraint is on; zero if not a domain constraintReferences:`pg_type.oid`
`conindid`	`oid`NOT NULL	The index supporting this constraint, if it's a unique, primary key, foreign key, or exclusion constraint; else zeroReferences:`pg_class.oid`
`conparentid`	`oid`NOT NULL	The corresponding constraint of the parent partitioned table, if this is a constraint on a partition; else zeroReferences:`pg_constraint.oid`
`confrelid`	`oid`NOT NULL	If a foreign key, the referenced table; else zeroReferences:`pg_class.oid`
`confupdtype`	`"char"`NOT NULLDocumented as:`char`	Foreign key update action code: a = no action, r = restrict, c = cascade, n = set null, d = set default
`confdeltype`	`"char"`NOT NULLDocumented as:`char`	Foreign key deletion action code: a = no action, r = restrict, c = cascade, n = set null, d = set default
`confmatchtype`	`"char"`NOT NULLDocumented as:`char`	Foreign key match type: f = full, p = partial, s = simple
`conislocal`	`boolean`NOT NULLDocumented as:`bool`	This constraint is defined locally for the relation. Note that a constraint can be locally defined and inherited simultaneously.
`coninhcount`	`smallint`NOT NULLDocumented as:`int2`	The number of direct inheritance ancestors this constraint has. A constraint with a nonzero number of ancestors cannot be dropped nor renamed.
`connoinherit`	`boolean`NOT NULLDocumented as:`bool`	This constraint is defined locally for the relation. It is a non-inheritable constraint.
`conperiod`	`boolean`NOT NULLDocumented as:`bool`	This constraint is defined with WITHOUT OVERLAPS (for primary keys and unique constraints) or PERIOD (for foreign keys).
`conkey`	`smallint[]`Documented as:`int2[]`	If a table constraint (including foreign keys, but not constraint triggers), list of the constrained columnsReferences:`pg_attribute.attnum`
`confkey`	`smallint[]`Documented as:`int2[]`	If a foreign key, list of the referenced columnsReferences:`pg_attribute.attnum`
`conpfeqop`	`oid[]`	If a foreign key, list of the equality operators for PK = FK comparisonsReferences:`pg_operator.oid`
`conppeqop`	`oid[]`	If a foreign key, list of the equality operators for PK = PK comparisonsReferences:`pg_operator.oid`
`conffeqop`	`oid[]`	If a foreign key, list of the equality operators for FK = FK comparisonsReferences:`pg_operator.oid`
`confdelsetcols`	`smallint[]`Documented as:`int2[]`	If a foreign key with a SET NULL or SET DEFAULT delete action, the columns that will be updated. If null, all of the referencing columns will be updated.References:`pg_attribute.attnum`
`conexclop`	`oid[]`	If an exclusion constraint or WITHOUT OVERLAPS primary key/unique constraint, list of the per-column exclusion operators.References:`pg_operator.oid`
`conbin`	`pg_node_tree`	If a check constraint, an internal representation of the expression. (It's recommended to use pg_get_constraintdef() to extract the definition of a check constraint.)

Columns marked "implicit" are not included in SELECT *; some early statistics views are completed from same-version source, and the extra provenance is shown in the column description.

Common system columns 6

System attributes PostgreSQL provides, normally not expanded by SELECT *. Kept separately so they are not mixed in with ordinary columns.

Columns	Type	attnum
`tableoid`	`oid`	-6
`cmax`	`cid`	-5
`xmax`	`xid`	-4
`cmin`	`cid`	-3
`xmin`	`xid`	-2
`ctid`	`tid`	-1

Column evolution matrix

Hover a cell for the column type

PresentAdded / type or attribute changeRemoved

COLUMN	9.0	9.1	9.2	9.3	9.4	9.5	9.6	10	11	12	13	14	15	16	17	18	19
`oid`
`conname`
`connamespace`
`contype`
`condeferrable`
`condeferred`
`conrelid`
`contypid`
`conindid`
`confrelid`
`confupdtype`
`confdeltype`
`confmatchtype`
`conislocal`
`coninhcount`
`conkey`
`confkey`
`conpfeqop`
`conppeqop`
`conffeqop`
`conexclop`
`conbin`
`consrc`										−	–	–	–	–	–	–	–
`convalidated`	–
`connoinherit`	–	–
`conparentid`	–	–	–	–	–	–	–	–
`confdelsetcols`	–	–	–	–	–	–	–	–	–	–	–	–
`conenforced`	–	–	–	–	–	–	–	–	–	–	–	–	–	–	–
`conperiod`	–	–	–	–	–	–	–	–	–	–	–	–	–	–	–

9.0 is the coverage baseline; a column already present in the baseline is not marked as added. The matrix aligns columns by name, so a rename appears as the old column removed and a new column added.

Evolution timeline

Changes between adjacent majors

PostgreSQL 18 ← 17

+ conenforced+ conperiod3 column description updatesCatalog description updated

PostgreSQL 17 ← 16

1 column description updatesCatalog description updated

PostgreSQL 16 ← 15

~ coninhcount: integer → smallint

PostgreSQL 15 ← 14

+ confdelsetcols

PostgreSQL 14 ← 13

6 column description updates

PostgreSQL 12 ← 11

− consrc~ oid: implicit → ordinary2 column description updates

PostgreSQL 11 ← 10

+ conparentid

PostgreSQL 9.3 ← 9.2

2 column description updates

PostgreSQL 9.2 ← 9.1

+ connoinherit1 column description updates

PostgreSQL 9.1 ← 9.0

+ convalidated~ conbin: text → pg_node_tree

Compare any two versions

FROMTOInclude description updates

Pick two versions to see added, removed, and retyped columns. The interactive comparison needs JavaScript; the evolution timeline reads fine without it.