invalid input syntax for type

What it means

Values reach PostgreSQL as text: literals in your SQL, parameters from a driver, fields in a CSV. Each type has an input function that parses that text. This error means the parse failed. The value between the quotes is exactly what the parser saw:

ERROR:  invalid input syntax for type integer: ""

That’s an empty string, not a missing value. '' and NULL are different things in PostgreSQL.

The same error code covers enums (invalid input value for enum mood: "Happy"), JSON (invalid input syntax for type json, with a DETAIL naming the bad token) and arrays (malformed array literal). Dates and times use their own codes and messages, such as date/time field value out of range.

Common causes

1. An empty string for a number or uuid, typically an empty form field or CSV cell sent as ''.
2. A value in the wrong format for the type: 1.5 for an integer, 1,50 (decimal comma) for numeric, maybe for a boolean, Happy for an enum whose label is happy.
3. A placeholder from the application: undefined, null or NaN as text, sent as a uuid or integer parameter.
4. Casting a text column that holds some non-numeric values, such as raw_qty::int when a few rows say n/a. It works until the first bad row.
5. Comparing a numeric column with a string that isn’t a number: WHERE id = 'abc'. The string is converted to the column’s type, and fails.
6. A bad field in a CSV loaded with COPY.

How to fix it

Turn empty strings into NULL

SELECT NULLIF(raw_qty, '')::int FROM items;

Better still, have the application send NULL (or leave the column out) instead of ''.

Send values in the type’s format

• Integers: whole numbers like 42 or -7; spaces around them are fine. 1.5 needs numeric.
• Numeric: a full stop for the decimal point, no thousands separators. Convert 1,50 in the application, or with replace(value, ',', '.')::numeric.
• Boolean: true/false, t/f, yes/no, on/off, 1/0.
• UUID: 32 hex digits, usually as xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.
• Enums: the label exactly as created, case included.

Find the rows that won’t convert

On PostgreSQL 16 and later:

SELECT id, raw_qty
FROM items
WHERE NOT pg_input_is_valid(raw_qty, 'integer');

pg_input_error_info(value, 'integer') returns the message the cast would raise. On older versions, use a pattern:

SELECT id, raw_qty FROM items WHERE raw_qty !~ '^\s*-?\d+\s*$';

Convert only what converts

To read a messy text column as numbers, leaving the rest NULL:

SELECT id,
       CASE WHEN pg_input_is_valid(raw_qty, 'integer') THEN raw_qty::int END AS qty
FROM items;

When you change the column’s type for good, clean the bad values first, then convert with ALTER TABLE items ALTER COLUMN raw_qty TYPE integer USING NULLIF(raw_qty, '')::integer. That rewrites the table; see changing a column’s type.

Skip bad lines in a CSV

COPY reports the line and column in its CONTEXT line. On PostgreSQL 17 and later, COPY … WITH (ON_ERROR ignore) skips rows that don’t convert and tells you how many it skipped; earlier versions reject the option.

Reproduce it

On PostgreSQL 18.6:

CREATE TYPE mood AS ENUM ('happy', 'sad');
CREATE TABLE items (id int PRIMARY KEY, qty int, price numeric, token uuid, active boolean,
                    feeling mood, raw_qty text, data jsonb);

INSERT INTO items (id, qty) VALUES (1, '');

ERROR:  invalid input syntax for type integer: ""
LINE 1: INSERT INTO items (id, qty) VALUES (1, '');
                                               ^

Other types, one statement each:

ERROR:  invalid input syntax for type integer: "1.5"
ERROR:  invalid input syntax for type numeric: "1,50"
ERROR:  invalid input syntax for type uuid: "undefined"
ERROR:  invalid input syntax for type boolean: "maybe"
ERROR:  invalid input value for enum mood: "Happy"

ERROR:  invalid input syntax for type json
LINE 1: INSERT INTO items (id, data) VALUES (7, '{name: "x"}');
                                                ^
DETAIL:  Token "name" is invalid.
CONTEXT:  JSON data, line 1: {name...

'12 ' (with a trailing space) is accepted as an integer. A text column with one bad value among good ones fails as soon as the cast reaches it, with no position:

INSERT INTO items (id, raw_qty) VALUES (10, '5'), (11, 'n/a'), (12, ''), (13, '7');
SELECT id, raw_qty::int FROM items WHERE raw_qty IS NOT NULL;

ERROR:  invalid input syntax for type integer: "n/a"

pg_input_is_valid(raw_qty, 'integer') returns false for rows 11 and 12. A CSV with a word in a number column:

ERROR:  invalid input syntax for type integer: "five"
CONTEXT:  COPY items, line 2, column qty: "five"

With ON_ERROR ignore on PostgreSQL 17 and 18, the same file loads its good rows and prints NOTICE: 1 row was skipped due to data type incompatibility; PostgreSQL 16 answers option "on_error" not recognized. pg_input_is_valid and pg_input_error_info don’t exist on 14 and 15. Otherwise PostgreSQL 14 to 17 print the same messages.

In Inlet

When a statement fails, Inlet shows the error at the position the server reports, with a hint for common ones. The inspector shows long text and JSON values in full, which helps when hunting for the one value that won’t parse. With your own Anthropic API key, Ask Claude (⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows.