Invalid Input Syntax For Type Integer On Load

The integer input function parses each text value into a 32-bit integer and raises 22P02 the moment a value is not a valid integer literal (non-digits, empty string, embedded spaces, thousands separators, decimals, or sentinels like N/A). INSERT ... SELECT runs as one statement inside an implicit transaction, so a single parse failure rolls back the whole load -- there is no partial commit and no row skipping.

The fix is therefore about the data, not the SQL: identify the values the integer parser cannot accept, then either filter them out of the load and quarantine them for review, or map them to a defined value (NULL or a default). Once only valid integers reach the cast, the load completes; the type itself never relaxes, which is exactly why a later bad value is rejected again. Once only valid integers reach the cast, the load completes; the type itself never relaxes, which is exactly why a later bad value is rejected again.

Free Tier -- Basic Approach

Before changing anything, size the problem with one count: how many staging rows are not valid integers, tested with the same anchored pattern the cast requires. Five. The cleanup is small and bounded, not an unknown.

SELECT count(*) AS non_integer_rows FROM staging_orders WHERE raw_qty !~ '^-?\d+$';

non_integer_rows
------------------
                5
(1 row)

SELECT count(*) AS non_integer_rows FROM staging_orders WHERE raw_qty !~ '^-?\d+$'; -- the anchored regex (optional sign then only digits) matches exactly what ::integer accepts, so the count equals what the load will reject.

SELECT raw_qty AS bad_value, count(*) AS occurrences
FROM staging_orders WHERE raw_qty !~ '^-?\d+$'
GROUP BY raw_qty ORDER BY occurrences DESC;

bad_value | occurrences
-----------+-------------
 N/A       |           5
(1 row)

SELECT line_no, raw_qty FROM staging_orders WHERE raw_qty !~ '^-?\d+$' ORDER BY line_no;

line_no | raw_qty
---------+---------
     996 | N/A
     997 | N/A
     998 | N/A
     999 | N/A
    1000 | N/A
(5 rows)

Split the batch instead of failing it. Load only the rows that are valid integers (INSERT 0 995) and copy the rest into a quarantine table for review (SELECT 5). The good data lands immediately and nothing is silently discarded.

INSERT INTO orders_clean(qty) SELECT raw_qty::integer FROM staging_orders WHERE raw_qty ~ '^-?\d+$';
CREATE TABLE orders_quarantine AS SELECT * FROM staging_orders WHERE raw_qty !~ '^-?\d+$';

INSERT 0 995
SELECT 5

INSERT INTO orders_clean(qty) SELECT raw_qty::integer FROM staging_orders WHERE raw_qty ~ '^-?\d+$'; then CREATE TABLE orders_quarantine AS SELECT * FROM staging_orders WHERE raw_qty !~ '^-?\d+$'; -- the filter and the quarantine predicate are exact complements, so every staging row goes exactly one way.

Confirm no data was lost and the type is still enforced. loaded (995) + quarantined (5) equals the staged total (1000), and casting a known-bad value directly still raises 22P02 -- proof the integer type was never relaxed, only the dirty rows were diverted.

SELECT (SELECT count(*) FROM orders_clean)      AS loaded,
       (SELECT count(*) FROM orders_quarantine) AS quarantined,
       (SELECT count(*) FROM staging_orders)    AS staged_total;

loaded | quarantined | staged_total
--------+-------------+--------------
    995 |           5 |         1000
(1 row)

SELECT 'N/A'::integer;

ERROR:  invalid input syntax for type integer: "N/A"
LINE 1: SELECT 'N/A'::integer;
               ^

Compare the three counts in one row (loaded + quarantined == staged_total), then run SELECT 'N/A'::integer to confirm it is still rejected with SQLSTATE 22P02 and a LINE pointer at the bad literal.

📦 Version Matrix (PG 14--18)

The behavior is identical on PostgreSQL 14 through 18: casting the dirty batch raises 'invalid input syntax for type integer', and filtering to valid rows loads them cleanly (INSERT 0 95). The integer input parser's 22P02 contract is stable across all supported majors.

Frequently Asked Questions

References

• PostgreSQL: Numeric Types (integer)
• PostgreSQL: Type Casts and CAST
• PostgreSQL: Error Codes (Class 22 -- Data Exception, 22P02 invalid_text_representation)
• PostgreSQL: Pattern Matching (POSIX regular expressions)