CHECK Constraint | PostgreSQL Tutorial

A CHECK constraint lets you enforce custom data integrity rules at the database level. It evaluates a boolean expression whenever a row is inserted or updated — if the expression returns FALSE, the operation is rejected. This ensures invalid data never enters the table, regardless of the application layer.

Defining CHECK constraints

As a column constraint (inline):

CREATE TABLE employees (
  id          SERIAL PRIMARY KEY,
  first_name  VARCHAR(50) NOT NULL,
  last_name   VARCHAR(50) NOT NULL,
  birth_date  DATE NOT NULL,
  joined_date DATE NOT NULL,
  salary      NUMERIC CHECK (salary > 0)
);

PostgreSQL auto-names inline constraints using the format {table}_{column}_check, e.g. employees_salary_check.

As a named table constraint (recommended for clarity):

CREATE TABLE employees (
  ...
  CONSTRAINT salary_positive CHECK (salary > 0)
);

Practical examples

Attempting to insert a row with a negative salary violates the constraint:

INSERT INTO employees (first_name, last_name, birth_date, joined_date, salary)
VALUES ('John', 'Doe', '1972-01-01', '2015-07-01', -100000);

-- ERROR:  new row for relation "employees" violates check constraint
-- "employees_salary_check"

Add a constraint to an existing table to ensure the join date is later than the birth date:

ALTER TABLE employees
ADD CONSTRAINT joined_date_check
CHECK (joined_date > birth_date);

A multi-column check with a function — require the first name to be at least 3 characters:

ALTER TABLE employees
ADD CONSTRAINT first_name_check
CHECK (LENGTH(TRIM(first_name)) >= 3);

INSERT INTO employees (first_name, last_name, birth_date, joined_date, salary)
VALUES ('Ab', 'Doe', '1990-01-01', '2008-01-01', 100000);

-- ERROR:  new row for relation "employees" violates check constraint
-- "first_name_check"

Removing a CHECK constraint

ALTER TABLE employees DROP CONSTRAINT joined_date_check;

Production tips

Always name CHECK constraints explicitly — auto-generated names like employees_salary_check are hard to reference in migrations and error messages.
CHECK constraints are re-evaluated on every INSERT and UPDATE, so keep expressions lightweight. Avoid subqueries or volatile functions (e.g., now()) in CHECK expressions.
Adding a CHECK constraint with ALTER TABLE validates all existing rows. Use NOT VALID to skip the initial scan and validate later: ALTER TABLE t ADD CONSTRAINT c CHECK (x > 0) NOT VALID; then ALTER TABLE t VALIDATE CONSTRAINT c;
A NULL value in a CHECK expression evaluates to NULL (not FALSE), so the constraint passes for NULLs. Combine CHECK with NOT NULL when both conditions are required.

Frequently Asked Questions

Does a PostgreSQL CHECK constraint fire on UPDATE as well as INSERT?

Yes. CHECK constraints are evaluated on both INSERT and UPDATE operations. If an UPDATE would set a column to a value that violates the constraint, the update is rejected.

What happens when a CHECK constraint expression returns NULL?

A NULL result is treated as passing the constraint (not as a failure). If you want to reject NULLs, add a separate NOT NULL constraint on the column.

Can I use functions inside a CHECK constraint expression?

Yes. You can use most deterministic functions, including LENGTH(), TRIM(), and other built-in functions. Avoid volatile functions like now() or random() because their results change with each evaluation, which can cause inconsistent constraint behaviour.

How do I add a CHECK constraint to a large table without locking it for a long time?

Use NOT VALID to add the constraint without scanning existing rows: ALTER TABLE t ADD CONSTRAINT c CHECK (condition) NOT VALID; Then validate it separately (which takes a weaker lock): ALTER TABLE t VALIDATE CONSTRAINT c; New inserts and updates are checked immediately; the validation step checks only pre-existing rows.

How do I find the names of CHECK constraints on a table?

Run \d table_name in psql to list all constraints. Alternatively, query information_schema.check_constraints or pg_constraint filtered by contype = 'c'.