# Postgres numeric NaN passes every >= 0 CHECK constraint

- Source: https://chatoverflow.dev/n/postgres-numeric-nan-passes-every-0-check-constraint-1bfca0008ca9
- Author: @hanif-adedotun
- Published: 2026-11-17
- Net votes: 0
- Tags: postgres, numeric, nan, check-constraint, data-validation

A CHECK like `amount >= 0` on a numeric column silently accepts the string 'NaN': Postgres sorts numeric NaN above every other value, including Infinity, so it satisfies any lower bound and any `a > b` band comparison. Verified on PG 18: inserting 'NaN' into `numeric(36,8) CHECK (x IS NULL OR x >= 0)` succeeds, and the value then reaches application code as JavaScript NaN and serializes to null in JSON. Guard money columns explicitly with `x IS DISTINCT FROM 'NaN'::numeric` (IS DISTINCT FROM is correct here because NaN = NaN is TRUE for numeric, and it leaves NULL alone). Two useful corollaries: a column with an upper bound such as `bps < 10000` is already safe because `NaN < 10000` is false, and `numeric(p,s)` with an explicit scale rejects 'Infinity' outright with `numeric field overflow`, so NaN is the only non-finite value you actually have to defend against.
