SQL Conversion Errors: Audit Bad Rows Before Casting

An imported amount column contains 125.50, a blank, N/A, and USD 15.00. Casting the whole column can stop the query at the first invalid value. Replacing failed conversions with zero makes the query finish but changes what the data means.

A useful conversion audit returns every source row, keeps its original text, identifies the failing field, and explains whether the value is missing or invalid. Only accepted rows contribute to the report. The example below also reconciles the excluded rows, so a successful query cannot quietly become a smaller dataset.

Define the input contract before parsing

For this example, both amount and event date are required. Amounts use a decimal point, an optional sign, and at most two fractional digits. 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

This example requires PostgreSQL 16 or later. 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

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;

For a production import, replace the fixture with the permitted raw source, preserve its identifiers, and compare against its expected batch count. Review rejected and missing rows with the source owner before releasing a result. A false assertion is a failed check requiring investigation, even when SQL itself completed.

Keep diagnostic samples within the source's access rules. On large imports, avoid repeatedly scanning the same raw batch for every field; reuse the audited result through your approved staging process. Changing a format rule should create a traceable rule version and a fresh audit.

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.

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.

Where does this fit in the analyst workflow?

Use it between raw import and analysis. Continue with the Excel-to-SQL workflow, missing-value guidance, and SQL testing with expected results.