pg_constraint — PostgreSQL system catalog

The PostgreSQL pg_constraint system catalog: full column reference (names, types, descriptions), catalog relationships and version support.

The Sev-1 Admin
The Sev-1 Admin  ·  3 min read

Summary

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.)

(Description quoted from the official PostgreSQL documentation.)

Columns

The pg_constraint system catalog exposes the following columns (names, types and descriptions are taken verbatim from the PostgreSQL documentation):

  • oid oid
    Row identifier
  • conname name
    Constraint name (not necessarily unique!)
  • connamespace oid references pg_namespace.oid
    The OID of the namespace that contains this constraint
  • contype 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 bool
    Is the constraint deferrable?
  • condeferred bool
    Is the constraint deferred by default?
  • conenforced bool
    Is the constraint enforced?
  • convalidated bool
    Has the constraint been validated?
  • conrelid oid references pg_class.oid
    The table this constraint is on; zero if not a table constraint
  • contypid oid references pg_type.oid
    The domain this constraint is on; zero if not a domain constraint
  • conindid oid references pg_class.oid
    The index supporting this constraint, if it’s a unique, primary key, foreign key, or exclusion constraint; else zero
  • conparentid oid references pg_constraint.oid
    The corresponding constraint of the parent partitioned table, if this is a constraint on a partition; else zero
  • confrelid oid references pg_class.oid
    If a foreign key, the referenced table; else zero
  • confupdtype char
    Foreign key update action code: a = no action, r = restrict, c = cascade, n = set null, d = set default
  • confdeltype char
    Foreign key deletion action code: a = no action, r = restrict, c = cascade, n = set null, d = set default
  • confmatchtype char
    Foreign key match type: f = full, p = partial, s = simple
  • conislocal bool
    This constraint is defined locally for the relation. Note that a constraint can be locally defined and inherited simultaneously.
  • coninhcount int2
    The number of direct inheritance ancestors this constraint has. A constraint with a nonzero number of ancestors cannot be dropped nor renamed.
  • connoinherit bool
    This constraint is defined locally for the relation. It is a non-inheritable constraint.
  • conperiod bool
    This constraint is defined with WITHOUT OVERLAPS (for primary keys and unique constraints) or PERIOD (for foreign keys).
  • conkey int2[] references pg_attribute.attnum
    If a table constraint (including foreign keys, but not constraint triggers), list of the constrained columns
  • confkey int2[] references pg_attribute.attnum
    If a foreign key, list of the referenced columns
  • conpfeqop oid[] references pg_operator.oid
    If a foreign key, list of the equality operators for PK = FK comparisons
  • conppeqop oid[] references pg_operator.oid
    If a foreign key, list of the equality operators for PK = PK comparisons
  • conffeqop oid[] references pg_operator.oid
    If a foreign key, list of the equality operators for FK = FK comparisons
  • confdelsetcols int2[] references pg_attribute.attnum
    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.
  • conexclop oid[] references pg_operator.oid
    If an exclusion constraint or WITHOUT OVERLAPS primary key/unique constraint, list of the per-column exclusion operators.
  • 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.)

Related catalogs

This object references the following other system catalogs:

Version applicability

Present in PostgreSQL 17, 18, 19 (verified against each release’s documentation). This is a long-standing system object that also exists in earlier PostgreSQL releases.

Related & references

Reference: PostgreSQL documentation — pg_constraint.