Why can a PostgreSQL INSERT fail a CHECK involving an omitted column?

Because PostgreSQL checks the proposed row—not just the values explicitly supplied by your application. Defaults must satisfy constraints, and a CHECK can reference multiple columns. A failed check can therefore involve a column the application never supplied. PostgreSQL’s constraint documentation describes both behaviors.

The useful debugging question is: What does the complete check expression evaluate to for the proposed row? For a specific failure, start with the actual INSERT, table definition, and complete error message rather than assuming omission is the cause.

Omitted columns still receive values

For an ordinary column omitted from the INSERT column list, PostgreSQL uses its declared default, or NULL if it has no default. Omission does not mean the column is excluded from the row. See the INSERT reference.

Consider this illustrative schema and statement:

CREATE TABLE inventory_example (
    sku text,
    quantity integer DEFAULT 0,
    CONSTRAINT positive_quantity CHECK (quantity > 0)
);

INSERT INTO inventory_example (sku)
VALUES ('demo-item');

Although the statement supplies only sku, the proposed row has quantity = 0. The expression quantity > 0 is therefore FALSE, so the insert violates positive_quantity.

The default and the constraint express incompatible rules for this insert: one supplies zero, while the other requires a positive value. PostgreSQL explicitly states that constraints apply even when a value comes from a default definition. Source: Constraints

A CHECK can validate a relationship between columns

A check failure is not necessarily about one column in isolation. PostgreSQL supports expressions comparing several columns, including the documented example of requiring a discounted price to be lower than a regular price. Source: Constraints

Here is an illustrative variation:

CREATE TABLE offers_example (
    price numeric,
    discounted_price numeric DEFAULT 10,
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

INSERT INTO offers_example (price)
VALUES (8);

The default supplies discounted_price = 10, while the application supplies price = 8. The check evaluates 8 > 10, which is FALSE. It is the relationship between the values that violates the rule.

Read the full constraint expression rather than inferring its meaning from its name or placement in the table definition. PostgreSQL even permits a check written beside one column to reference other columns, although its documentation advises against that style for portability. Source: Constraints

NULL does not automatically fail a CHECK

Do not assume that an omitted column became NULL and therefore failed the check.

PostgreSQL considers a CHECK satisfied when its expression evaluates to either TRUE or SQL NULL. It rejects a row when the expression evaluates to FALSE. For a comparison such as quantity > 0, a null quantity produces a null result—not FALSE. Source: Constraints

Check expression	Value of quantity	Expression result	Check outcome
quantity > 0	1	TRUE	Passes
quantity > 0	0	FALSE	Fails
quantity > 0	NULL	NULL	Passes

If the intended rule is “quantity must be present and positive,” express both requirements in the column definition:

quantity integer NOT NULL CHECK (quantity > 0)

The constraint documentation recommends NOT NULL to prevent null values. A positivity check alone does not enforce that requirement. Source: Constraints

A BEFORE trigger can change the proposed row

Defaults are not the only place to investigate. A row-level BEFORE INSERT trigger can modify the row: the row it returns becomes the row to insert. A value in the application’s statement may therefore differ from the value ultimately proposed for storage. Source: Overview of Trigger Behavior

If the supplied values and defaults appear consistent with the check, inspect applicable trigger definitions and their functions. Treat a trigger as a possible explanation to verify, not as a conclusion drawn from the error alone.

If a check references a stored generated column, account for its computed value too. PostgreSQL documents that stored generated columns are computed after BEFORE triggers and before AFTER triggers. Source: Overview of Trigger Behavior

Diagnose the constraint against the actual row

Use the documented behavior above to structure the investigation:

1. Capture the complete error and exact statement. Inspect bound parameter values rather than only a query template. Redact sensitive values before sharing diagnostic material.
2. Identify the failing constraint and read its full definition. List every column used by the expression; do not rely on the constraint’s name to explain its meaning.
3. Inspect the relevant column definitions. Check defaults and nullability requirements.
4. Account for every referenced value. Distinguish explicitly supplied values from omitted-column defaults, trigger changes, and any stored generated values.
5. Evaluate the complete Boolean expression. Determine whether it produces TRUE, FALSE, or SQL NULL; do not stop at whether an individual value looks reasonable.

Fix the mismatch without discarding the rule

Choose a repair based on the intended data model:

• Supply a valid value when the application is responsible for choosing it.
• Change the default when omission should legitimately produce a different value.
• Correct the constraint when its expression does not represent the intended rule.
• Correct trigger logic if it transforms an otherwise valid row into an invalid one.
• Use NOT NULL alongside value checks when absence itself is forbidden.

For the inventory example, supplying a positive quantity satisfies the check. Choosing a positive default is appropriate only if that default makes sense for the application. Allowing zero is appropriate only if zero is valid data.

The central lesson is simple: omitting a column does not omit it from validation. Reconstruct the proposed row, then evaluate the rule that rejected it.