PostgreSQL Constraint CHECK Failing on NULL Values, Unexpected Behavior

A CHECK constraint that seems to allow a NULL value through, despite the constraint's condition apparently not being satisfied by NULL, is actually correct, standard SQL behavior — PostgreSQL's three-valued logic means a CHECK constraint only rejects rows where the condition evaluates explicitly to false, and comparisons involving NULL evaluate to unknown, not false, letting the row through.

The Problem

A CHECK constraint intended to enforce a rule seems to have no effect for rows where the relevant column is NULL:

CREATE TABLE products (
    price NUMERIC,
    CHECK (price > 0)
);

INSERT INTO products (price) VALUES (NULL); -- succeeds, unexpectedly

The constraint clearly says price > 0, and NULL is obviously not greater than 0 in any everyday sense, yet the insert succeeds without any constraint violation error.

Why It Happens

SQL uses three-valued logic for boolean expressions: TRUE, FALSE, and UNKNOWN (produced by any comparison involving NULL). PostgreSQL's CHECK constraint specification is explicit and standard-compliant about this: a CHECK constraint is satisfied — and the row is allowed — if the expression evaluates to either TRUE or UNKNOWN. It's only actually rejected when the expression evaluates definitively to FALSE. Since price > 0 evaluates to UNKNOWN when price is NULL (you genuinely cannot determine whether an unknown value is greater than zero), the constraint treats this as satisfied, not violated — which is standard SQL behavior across most relational databases, not a PostgreSQL-specific quirk.

The Fix

If NULL should never be an acceptable value for this column at all, add an explicit NOT NULL constraint alongside the CHECK constraint — these are independent, complementary constraints, and NOT NULL is what actually prevents NULL values, not the CHECK constraint's own condition:

CREATE TABLE products (
    price NUMERIC NOT NULL,
    CHECK (price > 0)
);

With both constraints in place, a NULL price is now rejected by NOT NULL, and any non-null value that isn't genuinely greater than 0 is rejected by the CHECK constraint — together they enforce the full rule you actually intended.

If NULL is a legitimate, meaningful value for the column (representing "price not yet set," for example) but you still want the CHECK constraint to apply whenever a value IS provided, the current default behavior (allowing NULL through) is often already exactly what you want — no change needed, since this correctly distinguishes "no value provided yet" from "an invalid value provided."

If you need more nuanced handling — for example, requiring that NULL only be allowed in combination with some other column also being a specific value — explicitly handle the NULL case within the CHECK expression itself, rather than relying on the constraint's default NULL-passing behavior:

CHECK (
    (price IS NULL AND status = 'draft')
    OR (price IS NOT NULL AND price > 0)
)

This makes the intended handling of NULL explicit and self-documenting within the constraint itself, rather than relying on someone reading the code to already know about SQL's three-valued logic default behavior to correctly understand what the constraint actually permits.

When writing any new CHECK constraint, make it a habit to explicitly consider what should happen for NULL input, and write the constraint to reflect that decision deliberately, rather than allowing the standard three-valued logic default to apply implicitly without having actually decided whether that's the behavior you want.

Still Not Working?

If you've added NOT NULL but existing rows in the table already contain NULL values in that column, adding the constraint to an existing table fails until those existing rows are resolved first:

ALTER TABLE products ALTER COLUMN price SET NOT NULL;
-- ERROR: column "price" contains null values

Update or remove the existing NULL-valued rows before adding the constraint, choosing whichever resolution makes sense for your actual data:

UPDATE products SET price = 0 WHERE price IS NULL; -- or DELETE, or a genuine correct value
ALTER TABLE products ALTER COLUMN price SET NOT NULL;