Find Bad Numbers and Dates in PostgreSQL 16+

In PostgreSQL 16+, check raw text before casting it, retain the offending row and column, and reconcile every excluded record. This guide starts with a small error probe, then builds a complete audit for numeric amounts and dates. The examples use PostgreSQL-specific functions and casts.

The goal is a report you can explain: valid values stay usable, missing amounts remain unknown, and malformed values carry a reason. Replacing a conversion failure with zero can conceal the very row you need to investigate.

Find the failing values first

Run this self-contained query to see the source row, original value, PostgreSQL error message, and SQLSTATE. It reads five literal values and creates no tables.

WITH incoming(source_row, amount_raw) AS (
    VALUES (1, '125.50'), (2, 'USD 15.00'),
           (3, '10000000000.00'), (4, '1.234'), (5, NULL)
)
SELECT i.source_row, i.amount_raw, e.message, e.sql_error_code
FROM incoming AS i
CROSS JOIN LATERAL
    pg_input_error_info(i.amount_raw, 'numeric(12,2)') AS e
WHERE e.sql_error_code IS NOT NULL
ORDER BY i.source_row;

Expected results: row 2 (USD 15.00) has SQLSTATE 22P02, and row 3 (10000000000.00) has 22003. The message distinguishes invalid numeric syntax from overflow. PostgreSQL documents these error fields.

This is a type-error probe. Row 4, 1.234, can be converted with rounding, so it produces no error here. SQL null also produces no error. The full audit below adds the business rules that forbid rounding and require both fields.

Define the input contract before parsing

For this example, both amount and event date are required. Amounts have at least one digit before an optional decimal point, an optional leading sign, and one or two fractional digits when the point is present. The target is numeric(12,2). Currency symbols, grouping commas, scientific notation, and extra decimal places are rejected rather than guessed.

Dates must use four-digit YYYY-MM-DD and describe a real calendar date. A slash date such as 03/04/2026 is rejected because its intended month and day are unspecified. PostgreSQL accepts several date formats, with some interpretation depending on DateStyle; the explicit format gate removes that ambiguity. See the PostgreSQL date/time documentation.

Leading and trailing ordinary spaces may be trimmed for parsing. SQL null, an empty string after trimming, and the exact placeholder N/A mean missing. These are deliberate source rules, not universal cleaning defaults. A zero remains a valid amount; unknown amounts never become zero.

Check the actual target, then guard the cast

pg_input_is_valid tests a text value against a named target, including a numeric precision and scale. A regex alone cannot validate a calendar date or numeric range. PostgreSQL's input-validity documentation describes this function.

The separate scale rule matters: PostgreSQL normally rounds extra fractional digits to a declared numeric scale. Thus, “can fit in the type” and “satisfies our no-rounding contract” are different checks. A numeric(12,2) value has room for ten integer digits and two fractional digits. See the numeric type documentation.

Keep a dangerous cast inside its guarded CASE expression. Do not assume that a validation predicate written first in WHERE runs before another predicate's cast; PostgreSQL can reorganize expression evaluation. The expression evaluation rules explain why textual predicate order is insufficient.

Run a complete conversion audit

This query reads only twelve literal fixture rows. It creates no tables and modifies no database data. The CTEs normalize permitted placeholders, diagnose each field, assign one row status, and cast only fields with an ok reason.

WITH raw(source_row, amount_raw, date_raw) AS (
    VALUES
      (1, '125.50',         '2026-10-01'),
      (2, '-20.25',         '2026-10-02'),
      (3, '0',             '2026-10-03'),
      (4, ' 10.00 ',       ' 2026-10-04 '),
      (5, NULL,            '2026-10-05'),
      (6, 'N/A',           NULL),
      (7, '10000000000.00','2026-10-06'),
      (8, '1.234',         '2026-10-07'),
      (9, '9.00',          '2026-02-30'),
      (10, '11.00',        '03/04/2026'),
      (11, 'USD 15.00',    '2026-10-08'),
      (12, 'oops',         '')
), normalized AS (
    SELECT raw.*,
           NULLIF(NULLIF(btrim(amount_raw), ''), 'N/A') AS amount_text,
           NULLIF(NULLIF(btrim(date_raw), ''), 'N/A') AS date_text
    FROM raw
), diagnosed AS (
    SELECT normalized.*,
           CASE
             WHEN amount_text IS NULL THEN 'missing'
             WHEN amount_text !~ '^[+-]?[0-9]+([.][0-9]+)?$' THEN 'format'
             WHEN amount_text ~ '[.][0-9]{3,}$' THEN 'scale'
             WHEN NOT pg_input_is_valid(amount_text, 'numeric(12,2)') THEN 'range'
             ELSE 'ok'
           END AS amount_reason,
           CASE
             WHEN date_text IS NULL THEN 'missing'
             WHEN date_text !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN 'format'
             WHEN NOT pg_input_is_valid(date_text, 'date') THEN 'calendar'
             ELSE 'ok'
           END AS date_reason
    FROM normalized
), classified AS (
    SELECT diagnosed.*,
           CASE
             WHEN amount_reason NOT IN ('ok', 'missing')
               OR date_reason NOT IN ('ok', 'missing') THEN 'rejected'
             WHEN amount_reason = 'missing'
               OR date_reason = 'missing' THEN 'missing'
             ELSE 'accepted'
           END AS row_status
    FROM diagnosed
), typed AS (
    SELECT classified.*,
           CASE WHEN amount_reason = 'ok'
                THEN amount_text::numeric(12,2) END AS amount,
           CASE WHEN date_reason = 'ok'
                THEN date_text::date END AS event_date
    FROM classified
)
SELECT source_row, amount_raw, date_raw,
       amount_reason, date_reason, row_status, amount, event_date
FROM typed
ORDER BY source_row;

Raw text and source row numbers remain visible beside the typed values. In a real import, keep the file or batch identifier as well: row 12 is useful evidence only when someone can find the corresponding input.

Each field records its first failing rule. Amount checks run missing, format, scale, then range; date checks run missing, format, then calendar validity. This makes reasons stable and understandable. A row can have two field problems, but receives exactly one row status.

Understand the expected classifications

On narrow screens, scroll the tables sideways to see every column.

The row-status precedence is rejected before missing before accepted. Any invalid field makes the row rejected; otherwise, any required missing field makes it missing. Acceptance requires both fields to pass. Row 12 therefore appears once in the rejected count, while its field diagnostics still show the missing date.

A rejected row can have a valid amount but an invalid date, as row 9 does. Its typed amount is useful for diagnosis, but must not enter an accepted-row total. Field validity and row eligibility answer different questions.

Reconcile rows before calculating the result

The expected accounting is 12 source rows = 4 accepted + 6 rejected + 2 missing. Accepted amounts total 115.25: 125.50 minus 20.25 plus zero plus 10.00. Count rows by their single status rather than adding field-error counts; field errors can overlap.

Keep the same WITH block above and replace its final SELECT with this assertion query. Each check should return true.

SELECT
    count(*) = 12 AS source_count_ok,
    count(*) FILTER (WHERE row_status = 'accepted') = 4
      AND count(*) FILTER (WHERE row_status = 'rejected') = 6
      AND count(*) FILTER (WHERE row_status = 'missing') = 2 AS status_counts_ok,
    count(*) = count(*) FILTER (
        WHERE row_status IN ('accepted', 'rejected', 'missing')
    ) AS every_row_accounted_for,
    (sum(amount) FILTER (WHERE row_status = 'accepted'))
      IS NOT DISTINCT FROM 115.25::numeric AS accepted_total_ok
FROM typed;

A false assertion requires investigation even when SQL itself completed. When adapting the audit, replace these fixture-specific expectations with your batch contract; do not retain the number 12 as a production rule.

Apply the audit to an existing import

Suppose your existing staging table is staging.raw_orders, with text columns amount_raw and event_date_raw, plus batch_id and a source row identifier unique within that batch. Replace only the first raw CTE in the complete query with the fragment below; retain the leading WITH and the comma before normalized. Use your actual table, column names, and reviewed batch identifier.

raw AS (
    SELECT source_row, amount_raw, event_date_raw AS date_raw
    FROM staging.raw_orders
    WHERE batch_id = 'demo-batch-2026-10-11'
)

The batch filter belongs at the start so unrelated imports do not enter the audit. In application code, bind the batch value through your database driver's parameter API. Record the selected batch identifier with the results. This example assumes the raw columns are text; diagnose file decoding or CSV structure errors in the import step first.

Find which fields need attention

Keep the full WITH block and replace its final SELECT with the following. It returns one count per field and reason.

SELECT field_name, reason, count(*) AS affected_rows
FROM typed
CROSS JOIN LATERAL (
    VALUES ('amount', amount_reason), ('event_date', date_reason)
) AS field_issue(field_name, reason)
WHERE reason <> 'ok'
GROUP BY field_name, reason
ORDER BY field_name, reason;

For the twelve-row fixture, expect amount/format = 2, amount/missing = 2, amount/range = 1, amount/scale = 1, event_date/calendar = 1, event_date/format = 1, and event_date/missing = 2. These ten field issues affect eight rows. Summing field counts is not a rejected-row count.

Return the rows to the source owner

Use this alternative final query for the exception list. It includes both rejected and missing rows with their original values; the fixture returns rows 5 through 12.

SELECT source_row, amount_raw, date_raw,
       amount_reason, date_reason, row_status
FROM typed
WHERE row_status <> 'accepted'
ORDER BY source_row;

Reconcile the whole batch

For your own data, replace the fixture assertions with a summary and compare audited_rows to the independent count from the source export or ingestion manifest.

SELECT
    count(*) AS audited_rows,
    count(*) FILTER (WHERE row_status = 'accepted') AS accepted_rows,
    count(*) FILTER (WHERE row_status = 'rejected') AS rejected_rows,
    count(*) FILTER (WHERE row_status = 'missing') AS missing_rows,
    sum(amount) FILTER (WHERE row_status = 'accepted') AS accepted_total
FROM typed;

A count derived from the same already-filtered query cannot detect rows lost before it ran. Keep the raw batch stable while checking it, reconcile accepted + rejected + missing to the manifest, and agree how exceptions will be corrected before releasing the report. accepted_total describes accepted rows only; if none exist, the sum is null. Label a partial total as partial.

For large imports, retain the audited result through the existing staging process so these reports do not repeatedly validate the same batch. These CTEs organize the logic; they do not promise a materialized cache or a particular execution plan. Keep diagnostic samples under the source's access rules.

Know the validity function's limits

The example uses built-in numeric and date inputs. Input-validity functions depend on a type's input function reporting errors safely. A custom type without that support may still throw; do not assume every type or extension is covered. That limitation is explicit in the PostgreSQL reference.

CASE protects these row-dependent casts; it is not a universal shield against expressions evaluated earlier, such as invalid constant expressions at planning time. PostgreSQL documents this distinction under conditional expressions. On older PostgreSQL versions, use a tested ingestion validator or supported parsing approach rather than copying unavailable functions.

Test the boundaries of your contract

Before changing a parsing rule, add a case with an explicit expected classification. These values exercise the same audit logic:

Also check an empty batch and a batch with no accepted rows. Row counts should remain explainable, and a missing total should not silently become zero. Changing permitted whitespace, placeholders, or date formats is a versioned rule change, followed by another audit.

FAQ

Why not turn invalid values into null?

You can use a null typed value while retaining its raw value and reason. The problem is collapsing invalid and genuinely missing inputs into one unexplained null, then losing their counts during aggregation.

Should I automatically fix comma-separated amounts or slash dates?

Only under an agreed source convention. A comma can mean a thousands separator or a decimal separator. Confirm that convention, record the transformation, and rerun the audit.

Why does PostgreSQL say pg_input_is_valid does not exist?

Check your server version. The examples require PostgreSQL 16 or later. On an older server, use an ingestion validator supported by that environment; a numeric-looking regex alone will not validate numeric range or calendar dates.

For the surrounding workflow, continue with the Excel-to-SQL workflow, missing-value guidance, and SQL testing with expected results.