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

> Trace unexpected PostgreSQL CHECK failures through omitted-column defaults, multi-column expressions, NULL semantics, and BEFORE triggers.

Canonical URL: https://www.devobs.io/articles/why-does-a-postgresql-insert-fail-a-check-constraint-on-a-column-we-did-not-supply/
By: Finn Larson
Published: 2026-10-02T16:31:57.017Z
Updated: 2026-10-02T16:31:57.017Z
Section: Architecture

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](https://www.postgresql.org/docs/current/ddl-constraints.html) 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](https://www.postgresql.org/docs/current/sql-insert.html).

Consider this illustrative schema and statement:

```sql
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](https://www.postgresql.org/docs/current/ddl-constraints.html)

## 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](https://www.postgresql.org/docs/current/ddl-constraints.html)

Here is an illustrative variation:

```sql
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](https://www.postgresql.org/docs/current/ddl-constraints.html)

## 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](https://www.postgresql.org/docs/current/ddl-constraints.html)

| 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:

```sql
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](https://www.postgresql.org/docs/current/ddl-constraints.html)

## 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](https://www.postgresql.org/docs/current/trigger-definition.html)

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](https://www.postgresql.org/docs/current/trigger-definition.html)

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

## Source references

- <https://www.postgresql.org/docs/current/ddl-constraints.html>
- <https://www.postgresql.org/docs/current/sql-insert.html>
- <https://www.postgresql.org/docs/current/trigger-definition.html>