A Positive PostgreSQL CHECK Can Still Admit NaN

A schema review can go wrong while every database mechanism involved is working exactly as designed. Consider value numeric CHECK (value > 0). A reviewer may read that as a compact statement of a broader business contract: the value is present, finite, and strictly positive. PostgreSQL does not read intent into the expression. Under PostgreSQL 18 semantics, unconstrained numeric can represent NaN, positive infinity, and negative infinity; PostgreSQL also treats NaN as greater than every non-NaN numeric value. Separately, a CHECK is satisfied when its expression is true or null, so a nullable column can admit SQL NULL. Those facts mean the apparently obvious predicate can accept NaN, Infinity, and NULL in addition to ordinary positive finite numbers. The relevant behavior is documented in PostgreSQL 18’s numeric type rules and constraint semantics.

This article qualifies one explicit domain: every representable finite numeric value strictly greater than zero, and nothing else. The experiment is deliberately synthetic and disposable. It separates three questions that schema owners often collapse: can the type represent the value, what does the comparison evaluate to, and do all installed constraints admit the row? All result matrices below are expectations derived from PostgreSQL 18 documentation, not observations from a completed database run. The complete fixture is designed to fail when execution disagrees with its independently authored oracle. Documentation, expected results, and actual execution evidence remain separate throughout.

Specify finite, positive, and present as separate requirements

The authorized domain is precise: a value is valid only when it is a PostgreSQL numeric, is not SQL NULL, is not NaN, is not either infinity, and is strictly greater than zero. A finite positive decimal such as 10 is valid. Zero and every negative finite value are invalid. -Infinity, Infinity, and NaN are invalid even though the type can represent them. NULL is invalid because “present” is a separate requirement from “positive.” PostgreSQL 18’s numeric documentation describes all three special values, restricts stored infinity to unconstrained numeric, and places NaN above all non-NaN values.

That wording matters because database integrity is a contract, not a visual impression. The distinction is closely related to broader practice around data contracts and explicit quality rules: name the accepted population first, then evaluate whether DDL actually implements it. The linked article is adjacent process guidance, not authority for PostgreSQL semantics. The PostgreSQL manual is the authority here.

The seven-case manifest is intentionally small but boundary-focused. It contains F10 → '10', Z0 → '0', N1 → '-1', QNAN → 'NaN', PINF → 'Infinity', NINF → '-Infinity', and MISSING → SQL NULL. The MISSING row has an actual null raw_text and null parsed numeric; it is not the four-character text NULL. Keeping raw input separate from parsed value prevents a later audit from confusing provenance with typed state.

This qualification does not prove a universal rule about what numeric should mean in every application. NaN or infinity can be legitimate in another model. It does not discuss currency design, IEEE arithmetic in Python, text parsing policy beyond the fixed manifest, or rollout locking. It asks only whether three candidate PostgreSQL admission rules implement this declared finite-positive domain.

Record the PostgreSQL baseline and isolate the fixture

A repeatable qualification starts by recording the server that actually evaluates the expressions. The fixture therefore prints both SELECT version() and current_setting('server_version_num'), then refuses to continue unless the server major version is 18. The system-information documentation explains the server build and connection information used here. A versioned manual identifies the intended implementation contract; it does not establish which patch release is installed. This article makes no claim that PostgreSQL 18 is the newest release. Preserve the actual build string rather than substituting a documentation version label.

Use a disposable local PostgreSQL 18 database and a fresh psql session. The following Bash launcher requires an authenticated local role permitted to create the scratch database. Set PGHOST to your cluster’s existing Unix-socket directory, not a hostname; the SQL also rejects a TCP connection. Save the complete fixture from “Qualify a finite-positive predicate” as finite_positive_lab.sql. The launcher preserves that source, the client version, standard output, standard error, and psql’s exit status in a new evidence directory. An existing database with the same name causes creation to fail; nothing is dropped or overwritten.

#!/usr/bin/env bash

set -euo pipefail

: "${PGHOST:?Set PGHOST to your local PostgreSQL Unix-socket directory}"

case "$PGHOST" in

  /*) ;;

  *) printf '%s\n' 'PGHOST must be an absolute local socket path.' >&2; exit 2 ;;

esac

createdb --host="$PGHOST" finite_positive_lab

run_dir=$(mktemp -d ./finite-positive-evidence.XXXXXX)

cp finite_positive_lab.sql "$run_dir/fixture.sql"

psql --version >"$run_dir/client-version.txt"

if psql -X --host="$PGHOST" --dbname=finite_positive_lab \

    --set=ON_ERROR_STOP=1 --file="$run_dir/fixture.sql" \

    >"$run_dir/psql.stdout" 2>"$run_dir/psql.stderr"; then

  status=0

else

  status=$?

fi

printf '%s\n' "$status" >"$run_dir/exit-status.txt"

printf 'Evidence directory: %s; psql exit status: %s\n' "$run_dir" "$status"

exit "$status"

The SQL creates only temporary tables and views and changes session settings. PostgreSQL’s temporary-table rules explain their session-scoped lifetime. The scratch database itself remains after disconnection. The fixture uses no extensions, external datasets, production credentials, benchmarks, or destructive cleanup. The psql error policy keeps ON_ERROR_STOP enabled, while the per-case handlers catch only the anticipated failures. A syntax error, unexpected exception, or failed reconciliation terminates the script rather than becoming a misleading success message.

Keep psql autocommit enabled and do not wrap the script in BEGIN or use --single-transaction. The collection block must finish before a separate verification statement can fail. This preserves its committed temporary ledger long enough to print it; the captured files remain available after the session’s temporary objects disappear. These test checks belong to the qualification harness, not to a production-facing input-policy interface. Production admission is expressed by the actual column constraints.

No PostgreSQL server was available for execution of this example. Consequently, no version() string, passing run, or observed SQL output is claimed here. The supplied source and its expected matrices are a qualification plan; operational acceptance still requires a complete run on a recorded PostgreSQL 18 build.

Publish the input manifest and expected admissions

Canonical source manifest: authored raw inputs and their expected parsed values.

Before attempting a single insert, publish the oracle. That order prevents a common test-design failure: observing what the database did and then quietly redefining “expected” to match it. The independent expected identities for the three branches are:

The branch A result follows directly from two independent PostgreSQL rules. The documented numeric ordering makes NaN > 0 true; positive infinity is also above zero. NULL > 0 instead evaluates to unknown. PostgreSQL’s constraint documentation says a CHECK is satisfied by true or null, so that unknown result does not reject MISSING. Zero, -1, and negative infinity make the comparison false and therefore violate the check. Acceptance under A is a prediction about the installed rule, not approval under the finite-positive business contract.

Branch B repairs exactly one part of the contract: presence. PostgreSQL defines NOT NULL as a separate constraint that forbids a null column value, while the CHECK retains its original comparison semantics. MISSING therefore fails, but NaN and positive infinity still satisfy value > 0. PostgreSQL also cautions that the textual order in which constraints appear does not establish a check order, so the fixture validates outcomes rather than relying on sequencing folklore.

Branch C adds a strict upper bound against positive infinity. For ordinary finite positives, value > 0 and value < 'Infinity'::numeric are both true. Positive infinity fails the upper comparison because it is equal to the bound, not below it. NaN also fails that comparison because PostgreSQL’s numeric ordering places it above positive infinity. Zero, negatives, and negative infinity fail the lower comparison. NOT NULL remains necessary: ordinary comparisons with null produce unknown, so the two comparisons alone do not require presence.

Keep SQL NULL distinct from the text NULL

The raw manifest deliberately stores raw_text as nullable text and value as nullable numeric. That design lets MISSING preserve actual absence. A display layer may print an empty field, (null), or another marker, but the database state remains SQL NULL. This matters because ordinary comparison operators involving a null operand yield null/unknown, and Boolean tests such as IS NOT TRUE are specifically documented to convert false or unknown into a definite true result for audit purposes.

The manifest also separates literal representation from type admission. 'Infinity' is valid input syntax for unconstrained numeric; whether it belongs in this business domain is a different question. Likewise, NaN is a representable numeric special value; whether a predicate rejects it is determined by comparison semantics and the installed constraints. Treating “parser accepted it” as “business rule approved it” is exactly the category error this fixture is designed to expose.

Run the positive CHECK without a non-null rule

Branch A is the deceptive baseline:

value numeric CHECK (value > 0)

Nothing is broken in PostgreSQL when QNAN, PINF, and MISSING are retained. On the contrary, their acceptance demonstrates that the database is enforcing the rule that was actually declared. The gap is between the declared Boolean expression and the reviewer’s richer mental model. PostgreSQL’s own constraints chapter uses price numeric CHECK (price > 0) as an example and explains that a check is satisfied when its expression is true or null and that a separate not-null constraint is needed to reject nulls. The numeric chapter independently establishes the special-value ordering that causes NaN to satisfy the lower-bound comparison.

A robust harness must attempt all seven cases independently. Letting psql stop at the first expected rejection would produce only partial evidence: perhaps Z0 failed, but there would be no proof about N1, NINF, NaN, infinity, or null. The fixture therefore wraps each individual insert in a nested PL/pgSQL BEGIN … EXCEPTION block. PostgreSQL documents that changes made inside a block that raises a caught exception are rolled back while local PL/pgSQL variables retain their values, so the handler can capture diagnostics and the outer block can append a ledger record after the failed insert’s subtransaction has been undone.

The ledger is not a count-only report. Its primary key is (branch_id, input_id), yielding exactly 21 identities when collection completes. Each record retains the raw text, parsed value, acceptance status, expected status, SQLSTATE, available constraint name, table, column, and error message. The target tables provide a second oracle: accepted identities and values must be retained, rejected identities must be absent, and both directions of the comparison must agree with the published expectation.

The ledger write is outside the individual INSERT’s exception block, but an uncaught error later in the same outer DO would still roll back that DO’s table changes. For that reason, outcome comparison occurs in a subsequent statement, after the collector has completed and its ledger has been printed. Progress notices help locate an interrupted case in captured standard error; they are not a substitute for the complete ledger or a retained-state check.

Capture expected failures without aborting the experiment

The admission loop catches only check_violation and not_null_violation. PostgreSQL’s PL/pgSQL control-structures documentation permits named conditions and GET STACKED DIAGNOSTICS for RETURNED_SQLSTATE, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, and MESSAGE_TEXT. An unmatched exception propagates without being relabeled as an expected rejection. A diagnostic item not populated by the error returns an empty string; the ledger normalizes that missing diagnostic to SQL NULL. It does not invent information from a constraint’s declared name.

The PostgreSQL 18 error-code appendix assigns 23514 to check_violation and 23502 to not_null_violation. The PostgreSQL 18 executor’s error-reporting path supplies a constraint name for a CHECK failure but reports the table and column for an ordinary NOT NULL violation. Therefore the fixture requires the named CHECK for 23514, and the correct branch table plus column value for 23502. It records a NOT NULL constraint name if one is supplied, checking it when present, but does not demand that optional field. A wrong table, column, error class, or CHECK name remains a harness failure. This distinction prevents a valid null rejection from prematurely stopping the matrix because an unavailable diagnostic was mistaken for missing enforcement.

Read PostgreSQL numeric comparisons on their own terms

The fastest way to mis-review this schema is to import comparison intuitions from another numeric environment. PostgreSQL explicitly departs from the familiar “NaN is unequal to itself” pattern for numeric: it treats NaN values as equal and greater than every non-NaN value so they can participate consistently in sorting and B-tree indexes. Therefore the common detector x <> x does not detect PostgreSQL numeric NaN. The control query in the fixture makes that visible with 'NaN'::numeric = 'NaN'::numeric, 'NaN'::numeric <> 'NaN'::numeric, and lower/upper comparisons against zero and infinity.

The critical expected truth table is compact. NaN = NaN is true; NaN <> NaN is false; NaN > 0 is true; NaN < Infinity is false. Infinity > 0 is true, while Infinity < Infinity is false. -Infinity > 0 is false. A null value produces unknown for the ordinary comparisons. Those results explain all three branch populations without invoking Python, IEEE binary floating-point behavior, or string comparison. The experiment is strictly PostgreSQL unconstrained numeric.

This discipline is the same reason prepared-query predicate validation must separate a predicate’s logical properties from whatever a reviewer expects a planner to infer. The linked article concerns index eligibility, not value admission; the shared lesson is methodological: read PostgreSQL’s documented semantics and then test the exact expression.

A tempting repair is value > 0 AND value <> 'NaN'::numeric. It does exclude NaN, because PostgreSQL makes NaN equal to itself, but it does not establish finiteness: positive infinity is still greater than zero and is not equal to NaN. Nor does it establish presence when the column is nullable, because null comparisons remain unknown and a CHECK still accepts an unknown result. The correct design must cover all boundaries of the declared domain rather than name one surprising sentinel and stop.

Add NOT NULL and measure what remains admitted

Branch B adds the missing presence requirement:

value numeric NOT NULL CHECK (value > 0)

This is a real improvement and a deliberately incomplete one. The expected retained set becomes exactly F10, QNAN, PINF. MISSING now violates the not-null constraint; zero, -1, and negative infinity continue to violate the positive check. But no part of NOT NULL changes the ordering of NaN or infinity, so both remain admitted. PostgreSQL describes not-null as the constraint that simply prevents a column from assuming the null value; it is not a proxy for “ordinary finite number.”

This branch is useful because it separates two review defects that can otherwise get bundled into “the check is weak.” First, the original rule did not require presence. Second, the original positive comparison did not require finiteness. Adding NOT NULL repairs the first defect and leaves the second intact. A schema reviewer should be able to say exactly which population changed after each DDL change.

The expected failure identity matters too. MISSING must produce SQLSTATE 23502 for column value in branch_b. Z0, N1, and NINF must produce 23514 for b_value_positive. QNAN and PINF should produce no integrity exception because they are expected acceptances under B. A rejection for an unrelated constraint is not equivalent evidence. The collector records each anticipated-condition outcome, and the verification statement fails on any mismatch after the evidence has been printed. The PostgreSQL error codes identify the failure mechanism without depending on localized message wording.

Do not let unknown predicates disappear from the audit

Admission and audit predicates have different jobs. For the authorized finite-positive rule, the Boolean expression value > 0 AND value < 'Infinity'::numeric evaluates to unknown for SQL NULL. If an audit uses only WHERE NOT (predicate), that null case disappears: NOT unknown is still unknown, and a WHERE clause retains only true rows. PostgreSQL’s comparison functions and operators documentation describes the null comparisons; its logical-operator truth tables explain negation. A successful audit must deliberately include unknowns rather than assume that negating the admission expression finds every violation.

For an invalid-row audit, use:

WHERE (value > 0 AND value < 'Infinity'::numeric) IS NOT TRUE

IS NOT TRUE returns true for both false and unknown, so the MISSING identity remains visible. PostgreSQL documents that these Boolean predicates always return true or false and that IS NOT TRUE treats a null Boolean input as not true. This is not a replacement for NOT NULL on admission. It is the audit-side expression that intentionally includes unknowns in the violation population.

Qualify a finite-positive predicate

Branch C expresses the declared domain directly:

value numeric NOT NULL

CHECK (value > 0 AND value < 'Infinity'::numeric)

For unconstrained PostgreSQL numeric, this pair of strict comparisons plus NOT NULL has the desired effect under the documented ordering. The lower bound excludes zero, all finite negatives, and negative infinity. The strict upper bound excludes positive infinity because equality with the bound does not satisfy <. It also excludes NaN because PostgreSQL sorts NaN above every non-NaN value, so NaN < Infinity is false. Finally, NOT NULL rejects absence rather than letting the check’s unknown result count as satisfied.

The expected accepted set is therefore only F10. This is a stronger statement than “the happy-path insert succeeded.” Qualification requires the six negative cases to be attempted and rejected for the expected reasons, the ledger to contain all seven branch-C identities, and the actual retained table to equal the single-row oracle. The fixture checks all three layers.

Save the following complete SQL fixture as finite_positive_lab.sql and run it with the launcher above. It is executable source, not a console transcript. Its queries produce evidence only when executed. The final message is conditional on all checks completing; matching A and B’s expected behavior does not approve those weaker predicates for business use.

\set ON_ERROR_STOP on

\set AUTOCOMMIT on

\pset null '<SQL NULL>'

\pset pager off

SET client_min_messages = notice;

SET search_path = pg_temp, pg_catalog;

SELECT version() AS postgres_version;

SELECT current_setting('server_version_num')::integer AS server_version_num;

SELECT current_database() AS database_name, current_user AS run_user;

DO $$

BEGIN

  IF current_setting('server_version_num')::integer / 10000 <> 18 THEN

    RAISE EXCEPTION 'This fixture requires PostgreSQL major 18';

  END IF;

  IF inet_server_addr() IS NOT NULL THEN

    RAISE EXCEPTION 'Use a local Unix-domain socket for this fixture';

  END IF;

END;

$$;

CREATE TEMP TABLE source_manifest (

  input_id text PRIMARY KEY,

  raw_text text,

  value numeric

);

INSERT INTO source_manifest (input_id, raw_text, value)

SELECT input_id, raw_text, raw_text::numeric

FROM (VALUES

  ('F10', '10'), ('Z0', '0'), ('N1', '-1'),

  ('QNAN', 'NaN'), ('PINF', 'Infinity'), ('NINF', '-Infinity'),

  ('MISSING', NULL::text)

) AS raw(input_id, raw_text);

CREATE TEMP TABLE expected_admission (

  branch_id text NOT NULL CHECK (branch_id IN ('A', 'B', 'C')),

  input_id text NOT NULL REFERENCES source_manifest(input_id),

  should_accept boolean NOT NULL,

  expected_sqlstate text,

  expected_constraint_name text,

  PRIMARY KEY (branch_id, input_id)

);

-- Literal oracle: never derive these outcomes from the tested predicate.

-- NOT NULL names below identify the declared constraint, not a required

-- diagnostic field. Its table and column are checked separately.

INSERT INTO expected_admission VALUES

  ('A','F10',true,NULL,NULL),

  ('A','Z0',false,'23514','a_value_positive'),

  ('A','N1',false,'23514','a_value_positive'),

  ('A','QNAN',true,NULL,NULL),

  ('A','PINF',true,NULL,NULL),

  ('A','NINF',false,'23514','a_value_positive'),

  ('A','MISSING',true,NULL,NULL),

  ('B','F10',true,NULL,NULL),

  ('B','Z0',false,'23514','b_value_positive'),

  ('B','N1',false,'23514','b_value_positive'),

  ('B','QNAN',true,NULL,NULL),

  ('B','PINF',true,NULL,NULL),

  ('B','NINF',false,'23514','b_value_positive'),

  ('B','MISSING',false,'23502','b_value_not_null'),

  ('C','F10',true,NULL,NULL),

  ('C','Z0',false,'23514','c_value_finite_positive'),

  ('C','N1',false,'23514','c_value_finite_positive'),

  ('C','QNAN',false,'23514','c_value_finite_positive'),

  ('C','PINF',false,'23514','c_value_finite_positive'),

  ('C','NINF',false,'23514','c_value_finite_positive'),

  ('C','MISSING',false,'23502','c_value_not_null');

DO $$

BEGIN

  IF (SELECT count(*) FROM source_manifest) <> 7

     OR (SELECT count(*) FROM expected_admission) <> 21 THEN

    RAISE EXCEPTION 'Incomplete manifest or oracle';

  END IF;

END;

$$;

-- Publish raw inputs and the oracle before any branch insert.

SELECT input_id, raw_text, raw_text IS NULL AS raw_is_sql_null, value

FROM source_manifest ORDER BY input_id COLLATE "C";

SELECT branch_id, count(*) AS expected_total,

  array_agg(input_id ORDER BY input_id COLLATE "C") AS expected_total_ids,

  count(*) FILTER (WHERE should_accept) AS expected_accepted_count,

  array_agg(input_id ORDER BY input_id COLLATE "C")

    FILTER (WHERE should_accept) AS expected_accepted_ids,

  count(*) FILTER (WHERE NOT should_accept) AS expected_rejected_count,

  array_agg(input_id ORDER BY input_id COLLATE "C")

    FILTER (WHERE NOT should_accept) AS expected_rejected_ids

FROM expected_admission GROUP BY branch_id ORDER BY branch_id;

CREATE TEMP TABLE branch_a (

  input_id text PRIMARY KEY,

  value numeric,

  CONSTRAINT a_value_positive CHECK (value > 0)

);

CREATE TEMP TABLE branch_b (

  input_id text PRIMARY KEY,

  value numeric CONSTRAINT b_value_not_null NOT NULL,

  CONSTRAINT b_value_positive CHECK (value > 0)

);

CREATE TEMP TABLE branch_c (

  input_id text PRIMARY KEY,

  value numeric CONSTRAINT c_value_not_null NOT NULL,

  CONSTRAINT c_value_finite_positive

    CHECK (value > 0 AND value < 'Infinity'::numeric)

);

CREATE TEMP TABLE admission_ledger (

  branch_id text NOT NULL,

  input_id text NOT NULL,

  raw_text text,

  parsed_value numeric,

  accepted boolean NOT NULL,

  expected_accept boolean NOT NULL,

  sqlstate text,

  constraint_name text,

  table_name text,

  column_name text,

  error_message text,

  PRIMARY KEY (branch_id, input_id)

);

DO $$

DECLARE

  e record;

  s record;

  ok boolean;

  st text;

  con text;

  tab text;

  col text;

  msg text;

BEGIN

  FOR e IN

    SELECT * FROM expected_admission ORDER BY branch_id, input_id

  LOOP

    SELECT * INTO STRICT s FROM source_manifest

    WHERE input_id = e.input_id;

    ok := false;

    st := NULL; con := NULL; tab := NULL; col := NULL; msg := NULL;

    BEGIN

      CASE e.branch_id

        WHEN 'A' THEN

          INSERT INTO branch_a VALUES (s.input_id, s.value);

        WHEN 'B' THEN

          INSERT INTO branch_b VALUES (s.input_id, s.value);

        WHEN 'C' THEN

          INSERT INTO branch_c VALUES (s.input_id, s.value);

        ELSE

          RAISE EXCEPTION 'Unknown branch %', e.branch_id;

      END CASE;

      ok := true;

    EXCEPTION WHEN check_violation OR not_null_violation THEN

      GET STACKED DIAGNOSTICS

        st = RETURNED_SQLSTATE, con = CONSTRAINT_NAME,

        tab = TABLE_NAME, col = COLUMN_NAME, msg = MESSAGE_TEXT;

    END;

    -- Any unmatched exception propagates. Do not catch OTHERS as success.

    -- The individual failed INSERT has rolled back before this ledger write.

    INSERT INTO admission_ledger VALUES (

      e.branch_id, s.input_id, s.raw_text, s.value, ok, e.should_accept,

      st, NULLIF(con, ''), NULLIF(tab, ''), NULLIF(col, ''), NULLIF(msg, '')

    );

    RAISE NOTICE 'case %/% accepted=% SQLSTATE=% table=% column=% constraint=%',

      e.branch_id, s.input_id, ok, st, tab, col, con;

  END LOOP;

END;

$$;

-- In normal psql autocommit, the completed DO is now committed.

-- Print evidence before a separate verification statement can fail.

SELECT * FROM admission_ledger ORDER BY branch_id, input_id;

SELECT branch_id, count(*) AS total,

  array_agg(input_id ORDER BY input_id COLLATE "C") AS total_ids,

  count(*) FILTER (WHERE accepted) AS accepted_count,

  array_agg(input_id ORDER BY input_id COLLATE "C")

    FILTER (WHERE accepted) AS accepted_ids,

  count(*) FILTER (WHERE NOT accepted) AS rejected_count,

  array_agg(input_id ORDER BY input_id COLLATE "C")

    FILTER (WHERE NOT accepted) AS rejected_ids

FROM admission_ledger GROUP BY branch_id ORDER BY branch_id;

CREATE TEMP VIEW retained_rows AS

SELECT 'A'::text AS branch_id, input_id, value FROM branch_a

UNION ALL SELECT 'B', input_id, value FROM branch_b

UNION ALL SELECT 'C', input_id, value FROM branch_c;

SELECT branch_id, count(*) AS retained_count,

  array_agg(input_id ORDER BY input_id COLLATE "C") AS retained_ids

FROM retained_rows GROUP BY branch_id ORDER BY branch_id;

CREATE TEMP TABLE comparison_ledger (

  control_id text PRIMARY KEY,

  actual_result boolean,

  expected_result boolean

);

INSERT INTO comparison_ledger VALUES

  ('nan_equals_nan', 'NaN'::numeric = 'NaN'::numeric, true),

  ('nan_not_equals_nan', 'NaN'::numeric <> 'NaN'::numeric, false),

  ('nan_gt_zero', 'NaN'::numeric > 0, true),

  ('nan_lt_pos_inf', 'NaN'::numeric < 'Infinity'::numeric, false),

  ('pos_inf_gt_zero', 'Infinity'::numeric > 0, true),

  ('pos_inf_lt_itself', 'Infinity'::numeric < 'Infinity'::numeric, false),

  ('neg_inf_gt_zero', '-Infinity'::numeric > 0, false),

  ('null_gt_zero', NULL::numeric > 0, NULL::boolean);

SELECT * FROM comparison_ledger ORDER BY control_id;

CREATE TEMP VIEW naive_control AS

SELECT input_id, value, value > 0 AS gt_zero,

  value <> 'NaN'::numeric AS not_nan,

  (value > 0 AND value <> 'NaN'::numeric) AS naive_predicate

FROM source_manifest;

SELECT *, naive_predicate IS NOT FALSE AS nullable_check_would_admit

FROM naive_control ORDER BY input_id;

-- SQL NULL is explicit in this three-valued-logic audit control.

SELECT value, predicate, NOT predicate AS plain_not,

  predicate IS NOT TRUE AS null_inclusive_invalid

FROM (

  SELECT NULL::numeric AS value,

    (NULL::numeric > 0 AND NULL::numeric < 'Infinity'::numeric) AS predicate

) AS null_control;

CREATE TEMP TABLE precision_probe (

  probe_id text PRIMARY KEY,

  value numeric(12,2)

);

CREATE TEMP TABLE precision_ledger (

  probe_id text PRIMARY KEY,

  accepted boolean NOT NULL,

  expected_accept boolean NOT NULL,

  sqlstate text,

  expected_sqlstate text,

  constraint_name text,

  error_message text,

  error_detail text

);

DO $$

DECLARE

  p record;

  ok boolean;

  st text;

  con text;

  msg text;

  detail text;

BEGIN

  FOR p IN

    SELECT * FROM (VALUES

      ('QNAN', 'NaN'::numeric, true, NULL::text),

      ('PINF', 'Infinity'::numeric, false, '22003')

    ) AS expected(probe_id, value, should_accept, expected_state)

  LOOP

    ok := false;

    st := NULL; con := NULL; msg := NULL; detail := NULL;

    BEGIN

      INSERT INTO precision_probe VALUES (p.probe_id, p.value);

      ok := true;

    EXCEPTION WHEN numeric_value_out_of_range THEN

      GET STACKED DIAGNOSTICS

        st = RETURNED_SQLSTATE, con = CONSTRAINT_NAME,

        msg = MESSAGE_TEXT, detail = PG_EXCEPTION_DETAIL;

    END;

    INSERT INTO precision_ledger VALUES (

      p.probe_id, ok, p.should_accept, st, p.expected_state,

      NULLIF(con, ''), NULLIF(msg, ''), NULLIF(detail, '')

    );

  END LOOP;

END;

$$;

SELECT * FROM precision_ledger ORDER BY probe_id;

SELECT * FROM precision_probe ORDER BY probe_id;

-- Source audit: six expected violations, including SQL NULL.

SELECT input_id, raw_text, value FROM source_manifest

WHERE (value > 0 AND value < 'Infinity'::numeric) IS NOT TRUE

ORDER BY input_id COLLATE "C";

-- Negative control: NOT alone misses MISSING.

SELECT input_id, raw_text, value FROM source_manifest

WHERE NOT (value > 0 AND value < 'Infinity'::numeric)

ORDER BY input_id COLLATE "C";

-- Stored A audit: only the three violations A actually retained.

SELECT a.input_id, s.raw_text, a.value

FROM branch_a AS a JOIN source_manifest AS s USING (input_id)

WHERE (a.value > 0 AND a.value < 'Infinity'::numeric) IS NOT TRUE

ORDER BY a.input_id COLLATE "C";

CREATE TEMP VIEW audit_populations AS

SELECT 'source'::text AS population, input_id, value FROM source_manifest

UNION ALL

SELECT 'retained_A', input_id, value FROM branch_a;

SELECT population, count(*) AS total,

  array_agg(input_id ORDER BY input_id COLLATE "C") AS total_ids,

  count(*) FILTER (WHERE (value > 0 AND value < 'Infinity'::numeric)

    IS TRUE) AS valid_count,

  array_agg(input_id ORDER BY input_id COLLATE "C")

    FILTER (WHERE (value > 0 AND value < 'Infinity'::numeric)

      IS TRUE) AS valid_ids,

  count(*) FILTER (WHERE (value > 0 AND value < 'Infinity'::numeric)

    IS NOT TRUE) AS invalid_count,

  array_agg(input_id ORDER BY input_id COLLATE "C")

    FILTER (WHERE (value > 0 AND value < 'Infinity'::numeric)

      IS NOT TRUE) AS invalid_ids

FROM audit_populations GROUP BY population ORDER BY population;

-- No surrounding BEGIN and no psql --single-transaction for this script.

DO $$

DECLARE

  ids text[];

BEGIN

  IF (SELECT count(*) FROM admission_ledger) <> 21 THEN

    RAISE EXCEPTION 'Admission ledger must contain exactly 21 cases';

  END IF;

  IF EXISTS (

    SELECT branch_id, input_id FROM admission_ledger

    GROUP BY branch_id, input_id HAVING count(*) <> 1

  ) THEN

    RAISE EXCEPTION 'Duplicate ledger identities';

  END IF;

  IF EXISTS (

    SELECT 1 FROM expected_admission AS e

    FULL JOIN admission_ledger AS l USING (branch_id, input_id)

    WHERE e.input_id IS NULL OR l.input_id IS NULL

       OR l.accepted IS DISTINCT FROM e.should_accept

       OR l.expected_accept IS DISTINCT FROM e.should_accept

       OR l.sqlstate IS DISTINCT FROM e.expected_sqlstate

       OR (e.should_accept AND (

         l.constraint_name IS NOT NULL OR l.table_name IS NOT NULL

         OR l.column_name IS NOT NULL OR l.error_message IS NOT NULL))

       OR (NOT e.should_accept AND

         l.table_name IS DISTINCT FROM ('branch_' || lower(e.branch_id)))

       OR (e.expected_sqlstate = '23514' AND

         l.constraint_name IS DISTINCT FROM e.expected_constraint_name)

       OR (e.expected_sqlstate = '23502' AND (

         l.column_name IS DISTINCT FROM 'value'

         OR (l.constraint_name IS NOT NULL AND

           l.constraint_name IS DISTINCT FROM e.expected_constraint_name)))

  ) THEN

    RAISE EXCEPTION 'Admission outcome, coverage, or diagnostic mismatch';

  END IF;

  IF EXISTS (

    SELECT 1 FROM admission_ledger AS l

    JOIN source_manifest AS s USING (input_id)

    WHERE l.raw_text IS DISTINCT FROM s.raw_text

       OR l.parsed_value IS DISTINCT FROM s.value

  ) THEN

    RAISE EXCEPTION 'Ledger provenance differs from source manifest';

  END IF;

  IF EXISTS (

    SELECT branch_id, input_id, value FROM retained_rows

    EXCEPT

    SELECT e.branch_id, s.input_id, s.value

    FROM expected_admission AS e JOIN source_manifest AS s USING (input_id)

    WHERE e.should_accept

  ) OR EXISTS (

    SELECT e.branch_id, s.input_id, s.value

    FROM expected_admission AS e JOIN source_manifest AS s USING (input_id)

    WHERE e.should_accept

    EXCEPT SELECT branch_id, input_id, value FROM retained_rows

  ) THEN

    RAISE EXCEPTION 'Retained identities or values differ from oracle';

  END IF;

  IF (SELECT count(*) FROM comparison_ledger) <> 8 OR EXISTS (

    SELECT 1 FROM comparison_ledger

    WHERE actual_result IS DISTINCT FROM expected_result

  ) THEN

    RAISE EXCEPTION 'Numeric comparison control mismatch';

  END IF;

  IF (NULL::numeric > 0 AND NULL::numeric < 'Infinity'::numeric)

       IS NOT NULL THEN

    RAISE EXCEPTION 'NULL finite-positive predicate should be unknown';

  END IF;

  SELECT array_agg(input_id ORDER BY input_id COLLATE "C") INTO ids

  FROM naive_control WHERE naive_predicate IS NOT FALSE;

  IF ids IS DISTINCT FROM ARRAY['F10','MISSING','PINF']::text[] THEN

    RAISE EXCEPTION 'NaN-only exclusion control mismatch: %', ids;

  END IF;

  IF (SELECT count(*) FROM precision_ledger) <> 2 OR EXISTS (

    SELECT 1 FROM precision_ledger

    WHERE accepted IS DISTINCT FROM expected_accept

       OR sqlstate IS DISTINCT FROM expected_sqlstate

  ) THEN

    RAISE EXCEPTION 'Precision admission control mismatch';

  END IF;

  IF (SELECT count(*) FROM precision_probe) <> 1 OR NOT EXISTS (

    SELECT 1 FROM precision_probe

    WHERE probe_id = 'QNAN' AND value = 'NaN'::numeric

  ) THEN

    RAISE EXCEPTION 'Precision retained-state mismatch';

  END IF;

  SELECT array_agg(input_id ORDER BY input_id COLLATE "C") INTO ids

  FROM source_manifest

  WHERE (value > 0 AND value < 'Infinity'::numeric) IS NOT TRUE;

  IF ids IS DISTINCT FROM

       ARRAY['MISSING','N1','NINF','PINF','QNAN','Z0']::text[] THEN

    RAISE EXCEPTION 'Source audit mismatch: %', ids;

  END IF;

  SELECT array_agg(input_id ORDER BY input_id COLLATE "C") INTO ids

  FROM source_manifest

  WHERE NOT (value > 0 AND value < 'Infinity'::numeric);

  IF ids IS DISTINCT FROM ARRAY['N1','NINF','PINF','QNAN','Z0']::text[] THEN

    RAISE EXCEPTION 'Plain-NOT negative control mismatch: %', ids;

  END IF;

  SELECT array_agg(input_id ORDER BY input_id COLLATE "C") INTO ids

  FROM branch_a

  WHERE (value > 0 AND value < 'Infinity'::numeric) IS NOT TRUE;

  IF ids IS DISTINCT FROM ARRAY['MISSING','PINF','QNAN']::text[] THEN

    RAISE EXCEPTION 'Retained-A audit mismatch: %', ids;

  END IF;

END;

$$;

SELECT 'Fixture matched its oracle; only C fits the declared domain.' AS result;

Review every boundary rather than one happy-path insert

A seven-row fixture is not an exhaustive mathematical proof over every finite numeric; its value is boundary qualification. F10 tests that an ordinary finite positive remains usable. Z0 tests the open lower boundary. N1 tests an ordinary finite negative. NINF tests the special lower extreme. PINF tests the excluded upper sentinel. QNAN tests PostgreSQL’s non-obvious ordering rule. MISSING tests three-valued logic and the separate presence constraint. Together they target the specific ways this business rule can be weaker than intended. The general argument for other representable finite positives comes from the documented ordering, not from pretending seven examples enumerate the type.

Qualification requires every identity once, the expected acceptance status and applicable diagnostic fields, and retained identities and values matching the oracle in both directions. A count of “one accepted row” for C is insufficient if the wrong identity survived. A and B can match their expected matrices perfectly while still violating the business contract. Only C is a candidate for ACCEPT for this domain. The experiment succeeds by exposing the weaknesses of A and B as well as checking C, not by declaring every predictable branch safe.

Do not count a printed final message from an older run as evidence for a revised script. Keep the copied source beside the build string and both output streams. If a run stops before the final verification, retain its diagnostics as failure evidence, not as an incomplete success. Expected integrity errors are part of the experiment; a harness error changes the decision.

Test tempting repairs that solve a different problem

Two tempting “fixes” deserve explicit negative controls. The first is NaN-only exclusion. Because PostgreSQL makes NaN = NaN true, value <> 'NaN'::numeric does reject NaN for non-null values. But the conjunction value > 0 AND value <> 'NaN'::numeric still admits positive infinity. If the column is nullable, it also yields unknown for null, which a CHECK accepts. The fix names one surprising value rather than defining the intended finite interval. PostgreSQL’s documented NaN equality and null comparison rules make those failures predictable.

The second temptation is to replace unconstrained numeric with numeric(12,2) and treat precision as a domain repair. The numeric documentation restricts infinity to unconstrained numeric. PostgreSQL 18’s numeric implementation separately permits NaN regardless of the precision-and-scale type modifier, while an infinity with a declared modifier raises numeric_value_out_of_range. Thus the authored expectation is that QNAN is stored and PINF fails with SQLSTATE 22003. The fixture attempts them separately and captures the returned code, message, detail, and available constraint name. A precision-conversion error is not presented as a business CHECK rejection.

Precision-control expectations: two separate insertion attempts.

That distinction is operationally important. “Infinity cannot be stored in this physical type declaration” is not equivalent to “the schema enforces the finite-positive business rule.” A different constrained precision changes rounding and range behavior for ordinary finite values as well. It is a storage-precision decision with broader consequences. If the business domain is “all representable finite unconstrained numeric values greater than zero,” then imposing (12,2) would actually narrow the authorized finite population and change scale handling. It solves a different problem.

That distinction also explains why a rejection ledger needs more than a Boolean. Conversion to constrained numeric, CHECK enforcement, and NOT NULL enforcement are different mechanisms. PostgreSQL assigns distinct SQLSTATE codes to these cases: 22003 is a data exception, while 23514 and 23502 are both integrity-constraint violations. The precision probe catches only numeric_value_out_of_range. It then verifies both outcomes and its retained row in a separate statement, without comparing localized error text or converting an unexpected exception into a successful test.

Audit raw inputs and stored rows as different populations

Historical audit begins from the raw seven-row source population, not from whichever rows happened to survive branch A. The required predicate is:

WHERE (value > 0 AND value < 'Infinity'::numeric) IS NOT TRUE

Against all seven source values, the expected invalid IDs are Z0, N1, QNAN, PINF, NINF, MISSING; only F10 is valid. Against the retained branch-A table, the violation population is only QNAN, PINF, MISSING, because Z0, N1, and NINF were never admitted there. Those sets answer different questions and must not be merged. The first asks, “Which source inputs violate the authorized domain?” The second asks, “Which currently stored A rows violate it?” The table below summarizes authored expectations, not an observed historical audit.

The difference is the same reliability principle behind row-identity reconciliation in PostgreSQL updates: final counts are useful, but identities and provenance determine whether the right rows are present. That article addresses UPDATE ... FROM, not numeric predicates; here the analogous requirement is to keep raw spelling, parsed value, source ID, branch identity, and insertion outcome separately auditable.

The control WHERE NOT (value > 0 AND value < 'Infinity'::numeric) intentionally returns a smaller invalid set because MISSING evaluates the inner predicate to unknown and NOT unknown remains unknown. A WHERE clause does not select unknown. By contrast, IS NOT TRUE maps both false and unknown to true, which is why it is suitable for this null-inclusive violation audit. PostgreSQL documents both the ordinary null comparison behavior and the Boolean IS NOT TRUE predicate explicitly.

Keep aggregate totals subordinate to row identity

The fixture still records totals because they are useful reconciliation summaries: A should show seven attempted, four accepted, three rejected; B should show seven attempted, three accepted, four rejected; C should show seven attempted, one accepted, six rejected. But the corresponding identity arrays are primary evidence: F10, QNAN, PINF, MISSING for A; F10, QNAN, PINF for B; and F10 for C.

Why insist on both? A broken harness could substitute one accepted identity for another and preserve the count. A duplicate ledger row could inflate one case while a missing record hides another. A target table could contain an extra row and omit an expected one while preserving cardinality. The primary key, full coverage comparison, provenance checks, and bidirectional EXCEPT comparisons make those discrepancies testable. Retained comparisons include the numeric value as well as branch and input identity, so the right ID carrying the wrong value does not pass. Counts remain summaries of a row-level contract.

Quarantine invalid values without inventing replacements

Once the stronger rule is qualified, historical repair becomes an ownership problem, not a casting trick. The invalid raw-source identities are six distinct facts with different provenance: zero, finite negative, NaN, positive infinity, negative infinity, and missing. Nothing in the PostgreSQL type system tells you what business value should replace any of them. Silently converting NaN or null to zero would merely replace one out-of-domain value with another under this contract. Clamping infinity to a large finite number invents a magnitude the source never supplied. Deleting rows erases evidence and may destroy relationships or derived-state traceability.

The safer pattern is to preserve original identity, raw input, parsed value, and detection reason in a quarantine or review set controlled by the data owner. An owner-approved repair may obtain a corrected source value or reject the upstream record. An “unavailable” disposition belongs in quarantine or a separate workflow state, not as NULL in the now-required numeric column. Authorization comes from the domain, not from a pending constraint. A numeric value can be legitimate in another application; this article’s finite-positive rule is explicit and local, not universal.

Where derived outputs were computed from invalid historical values, repairing the base row is not enough. Recompute or revalidate affected outputs from preserved authorized source inputs under the corrected admission rule. Keep the old evidence until the owner accepts the replacement and the downstream reconciliation. “The row now passes the check” is not proof that reports, aggregates, caches, exports, or model inputs derived earlier are now trustworthy.

Cleanup and rollback are not synonyms. Ending the fixture session drops its temporary objects; it does not reverse already committed work elsewhere or remove the scratch database. Preserve the printed ledger and captured errors first. A real historical repair may have propagated beyond one transaction or database, so preservation must precede mutation. If provenance, intended domain, or downstream scope is unknown, choose HOLD rather than invent a replacement that merely makes the DDL pass.

Hand the qualified predicate to the deployment owner

Predicate correctness and deployment mechanics are separate review gates. From PostgreSQL 18’s numeric and constraint rules, this article derives NOT NULL plus CHECK (value > 0 AND value < 'Infinity'::numeric) as the candidate for the declared domain. It supplies a qualification fixture, not an executed production sign-off. A real version-recorded run is still required before claiming observed qualification. Neither the argument nor the tiny fixture establishes locking, runtime duration, phased rollout, or validation behavior for an existing production relation.

For that operational phase, use the existing guide to staging a PostgreSQL CHECK rollout rather than duplicating its NOT VALID and validation choreography here. The linked article focuses on introducing and validating a constraint across old and new row versions; this article’s handoff is narrower: give the deployment owner a predicate whose admitted value population has already been qualified.

The handoff packet should contain the exact DDL, seven-row manifest, prepublished A/B/C oracle, complete executed ledger, version() and server_version_num, client version, standard output, standard error, and process exit status. Include comparison-control and precision-probe output and keep the source and retained-A audit populations distinct. Preserve the exact fixture source used to produce the evidence, together with the run date and owner. A screenshot of “1 row inserted” cannot replace the chain from authorized inputs through rejected cases to retained state.

A deployment owner should also know what remains unproven. The fixture is not a concurrency test, benchmark, planner study, driver serialization test, or lock-acquisition rehearsal. It does not establish application compatibility for every writer. Those questions may matter later, but adding them to this lab would weaken rather than strengthen the evidence because they require different fixtures and owners.

Choose accept, repair, revalidate, or hold

A useful database-integrity decision has explicit evidence thresholds. Predicate approval and historical-data clearance are separate decisions. ACCEPT below applies only to a rule that implements the owner-approved domain, not merely to a branch whose behavior was predictable:

These decisions prevent two opposite failure modes. One is overconfidence: “the DDL executed, therefore the business rule is correct.” The other is indiscriminate cleanup: “the new check fails, therefore change the data until it passes.” A declarative constraint can be functioning exactly as specified while still expressing a weaker rule than reviewers intend. Conversely, an invalid value under this business rule is not automatically corrupt in a universal sense; it is out of contract for this column and needs owner-directed disposition.

The ACCEPT threshold is intentionally strict. All 21 branch/input identities must exist once, their outcomes and applicable diagnostics must match, and retained rows must reconcile. The source and retained-A audits remain separate, and the precision probe is not misrepresented as the repair. For this domain, the branch-C result is the candidate approval; A and B remain insufficient even when the harness reports an exact match. Unknown domain ownership, an incomplete run, or missing provenance means HOLD. Known invalid historical values require an authorized REPAIR, and affected downstream outputs require REVALIDATE.

Keep documented expectations separate from observed SQL output

There are three evidence classes in play. PostgreSQL 18’s documentation describes numeric values, constraint admission, null comparisons, and PL/pgSQL exception handling. That is a versioned implementation reference, not an execution record. The fixture is authored executable logic with an independent oracle and reconciliation rules. A real run is a third category: version(), server_version_num, ledger rows, and retained populations produced by a particular server build. Source-code inspection can clarify a diagnostic path, but it does not turn an unexecuted fixture into an observed test.

This example supplies documented expectations and fixture source, but no completed PostgreSQL server run. Every result matrix here is therefore labeled expected. Preserve that status when quoting the article: a predicted 21-case match is not an observed pass count. Once the complete fixture is executed on a recorded PostgreSQL 18 server, its captured output can support an environment-specific decision. That evidence remains limited to the declared preconditions; it does not establish identical behavior for every future release, application writer, or altered schema.

Evidence requiring investigation includes different comparison results, an unexpected SQLSTATE, an incorrect table or column attribution, an absent or wrong CHECK name, missing ledger identities, or a retained-value mismatch. An absent NOT NULL constraint-name diagnostic is not itself a failure when the required table, column, and SQLSTATE agree. A changed business definition also requires a new oracle. Stop and investigate discrepancies rather than editing expected results to match whatever the database happened to do.

Assign responsibility for the numeric contract

Domain ownership should be explicit because the database cannot decide the meaning of a quantity by itself. The domain owner defines whether zero is permitted, whether absence has meaning, and whether special values such as NaN or infinity are legitimate. The schema owner translates that rule into PostgreSQL types and constraints and maintains the qualification fixture. The pipeline owner preserves source identity and raw values, routes invalid inputs to owner-approved handling, and revalidates affected derivatives after repair. The reviewer checks that the oracle was published independently, the fixture was actually executed when execution is claimed, and the retained state matches the authorized domain.

The same ownership principle appears in other integrity surfaces such as SQLite foreign-key enforcement verification: a declaration, setting, or successful API call is not enough without evidence that the intended rule is active for the relevant operation. That linked workflow concerns SQLite connections, not PostgreSQL numerics, but it reinforces the separation between configured mechanism and verified contract.

Revalidation should be triggered when the numeric type declaration changes, the check expression changes, nullability changes, new input sources are introduced, parsing rules change, PostgreSQL major versions change, or downstream consumers begin depending on previously unmodeled special values. A fixture that once passed is not a permanent certificate after its preconditions change.

Responsibility also includes preserving negative controls. The NaN equality query, the positive-infinity bound checks, the nullable audit, and the numeric(12,2) probe are valuable because they guard against future reviewers “simplifying” the rule back into an assumption. Keep them near the schema contract or in its regression suite, with the same separation between expected and observed output.

Build deeper database integrity skills

The core result is narrow and operational. For PostgreSQL 18 unconstrained numeric, CHECK (value > 0) does not mean “finite, non-null, positive.” It admits NaN because PostgreSQL orders numeric NaN above non-NaN values; it admits positive infinity because infinity is greater than zero; and without NOT NULL it admits SQL null because a check is satisfied by true or unknown. A finite-positive-and-present contract can be expressed as numeric NOT NULL CHECK (value > 0 AND value < 'Infinity'::numeric), then qualified against the full seven-case oracle before acceptance.

The durable engineering habit is to keep representation, comparison, and admission separate. Publish expected identities before testing. Attempt every negative case independently. Capture the specific integrity condition. Reconcile retained rows, not just counts. Audit unknowns with null-aware Boolean predicates. Preserve raw evidence before repair, and let domain owners authorize meaning-changing corrections. Most importantly, distinguish what documentation says, what a fixture is designed to test, and what a real server actually produced.

Readers seeking structured practice in database integrity, SQL, and operational maintenance may review Refonte Learning’s Database Administrator Essentials program. Its published page states three months at 12–14 hours per week, recommends basic programming knowledge, and lists database design, SQL optimization, backup and recovery, performance tuning, security, and migration/integration. It also describes mentorship, practical work, a Training Certificate, and a Certificate of Internship upon successful completion. These are the provider’s program descriptions, not a promise of an external placement or of this exact PostgreSQL NaN/Infinity laboratory.