invalid input syntax for type json in PostgreSQL, measured

ERROR: invalid input syntax for type json means PostgreSQL was handed text to turn into json or jsonb, and the text is not JSON that type accepts. The message says type json for a jsonb column too, its SQLSTATE is 22P02 (invalid_text_representation), and DETAIL names the first token the parser could not use, such as Token "'" is invalid. A cast, an insert with a parameter or an ALTER does not tell you the table, column or row; only COPY adds them to CONTEXT.

We fed 38 strings to ::json and ::jsonb on PostgreSQL 16.15, 17.11 and 18.6, sent JavaScript and Python values through node-postgres and psycopg, and migrated a text column with bad rows to jsonb, on a table of nine rows and on one of a million. For the casts, every message, DETAIL and SQLSTATE was the same on all three versions; only the source line under LOCATION differed. One workaround did change in PostgreSQL 18, and we come to it below.

Which inputs raise it, and what does DETAIL say?

Most of what fails is JSON-shaped text from somewhere that is not a JSON serialiser: a Python str() of a dict, JavaScript’s NaN or undefined, a PostgreSQL array literal, a trailing comma. From pg_input_error_info, with the same result from a real cast:

SQL NULL cast to either type gave NULL, and the JSON text null gave JSON null, both without an error.

The primary message is invalid input syntax for type json for ::jsonb as well, so a search for “invalid input syntax for type jsonb” finds no message PostgreSQL actually writes. The one exception in the table is \u0000 in jsonb: a different SQLSTATE, 22P05 (untranslatable_character), and a different message, unsupported Unicode escape sequence. Code that catches 22P02 does not catch that one. Nor does it catch a number too large for numeric: {"a": 1e1000000} is valid json, and as jsonb it gave 22003, value overflows numeric format. Where both codes sit among the others is in our overview of SQLSTATE codes.

Where in the text is the error?

CONTEXT shows where the parser stopped, not where the value came from. With a 28,914-character input and the error at the end, it read JSON data, line 1: ...94, 4995, 4996, 4997, 4998, 4999, 5000], "bad": x.... In all three long inputs we tried, the part between the leading and trailing dots was 49 bytes and ended on the bad token: 49 characters of ASCII, but only 30 when they were all é. Nothing after the error is shown. line N counts line breaks: an error on the third line of a pretty-printed document said line 3.

Through a driver there is one more line, unnamed portal parameter $1 = '...', and the value is not in it. That is the default log_parameter_max_length_on_error = 0: with set log_parameter_max_length_on_error = -1 in the session, the same error showed $1 = '{"1","2"}', as it did in our uuid measurement.

Why does node-postgres send an array as invalid JSON?

Because node-postgres turns a JavaScript array into a PostgreSQL array literal before it knows where the value goes. Its documentation says so for parameters in general, “Converted to a string that describes a Postgres array”, and says that objects get JSON.stringify. Neither the page on queries nor the page on types warns that this applies to a json or jsonb column too. With pg 8.23.1 against PostgreSQL 18.6, [1, 2] went out as {"1","2"}, and the insert failed with 22P02 and DETAIL: Expected ":", but found ",".: the parser read the braces as the start of an object. An array of objects and a nested array failed with 22P02 as well. A json column, an explicit $1::jsonb and Kysely 0.29.8, which passed the array to pg unchanged, gave the same result.

The empty array is worse, because it works. [] went out as {}, which is valid JSON, and was stored as an empty object, in a json and in a jsonb column, with no error. Code that reads the value back expecting an array gets {}. An empty array inside an object is not affected: {a: []} went through JSON.stringify and was stored as {"a": []}.

The fix is to serialise arrays yourself: JSON.stringify([1, 2]) was stored as the array [1,2], in both column types and through Kysely. For objects that is what node-postgres already does, so the silent changes in the figure, NaN to null and keys with undefined dropped, are JSON.stringify’s and happen whichever way you send it.

What does psycopg do with a Python list or dict?

It depends on what is in the list. With psycopg 3.3.8 and no wrapper, [1, 2] was refused with 42804, column "jb" is of type jsonb but expression is of type smallint[], the error we take apart in column is of type jsonb but expression is of type text. ['a', 'b'] raised 22P02 with Token "a" is invalid. And [] was stored as {}, an empty object, with no error, as in node-postgres. Three outcomes for one type. psycopg’s dumper turned [] into the text {} with no type (OID 0), ['a', 'b'] into {a,b} with no type, and [1, 2] into {1,2} typed as smallint[]; that explains all three outcomes. We read that from the dumper and did not capture the wire.

So wrap the value in Jsonb(...), or Json(...) for a json column, and never build the text with str(). The wrapper does not protect you from NaN: a float nan inside the value still reached PostgreSQL as NaN. The wrapper serialises with Python’s json.dumps, which writes NaN by default; the dumper produced {"x": NaN}.

Why does jsonb reject a value that json accepted?

Because jsonb checks more. The documentation says json checks Unicode escapes “only for syntactic correctness”, while jsonb “rejects \u0000 (because that cannot be represented in PostgreSQL’s text type)” and insists on correct surrogate pairs. We saw exactly that: "\u0000" and "\ud800" were valid json and invalid jsonb.

The catch is that json does not keep you out of trouble, it postpones it. {"a": "x\u0000y"} went into a json column without an error and came back unchanged. Then ->> 'a' failed with 22P05, and so did -> 'a', which returns json and not text. So a migration from json to jsonb can stop on rows that have been read and written without complaint for years: casting that column to jsonb failed with 22P05. node-postgres produces such a row from an ordinary string: JSON.stringify({a: 'x\u0000y'}) was accepted by a json column and refused by a jsonb column with 22P05. Other differences between the two types are in json vs jsonb, measured.

How do you find the rows that are not valid JSON?

With pg_input_is_valid and pg_input_error_info, against the type you are migrating to. Both returned an answer for every row, including 22P05, instead of stopping the query:

select l.id, e.sql_error_code, e.message, e.detail
from legacy l, pg_input_error_info(l.payload, 'jsonb') e
where e.sql_error_code is not null;

On our nine-row table that listed four rows: {'a': 1} and {"a": NaN} with 22P02, "\u0000" with 22P05 and the empty string with 22P02. Rows with NULL gave NULL in every function, so a where not pg_input_is_valid(payload, 'jsonb') leaves them out.

IS JSON looks like the same check and is not. Over all 38 inputs it gave exactly the answers of pg_input_is_valid(txt, 'json'), and differed from pg_input_is_valid(txt, 'jsonb') on three: "\u0000", {"a": "x\u0000y"} and "\ud800". IS JSON called them valid, and a cast to jsonb failed on all three. If you check with IS JSON before a migration to jsonb, those rows pass the check and stop the ALTER. The same goes for a number beyond the range of numeric, which IS JSON accepted and jsonb refused with 22003. IS JSON WITH UNIQUE KEYS is the only one of the four that rejects duplicate keys, which jsonb accepts by keeping the last value. It also returned false for "\u0000" and "\ud800", which have no keys at all; the documentation of the predicate does not mention that. Our guess is that it decodes the strings and runs into the same Unicode check without raising it, but we did not look into that.

What the checks cost, as a count(*) over a table of 1,000,000 rows of about 60 bytes, 110 MB, with 10,000 invalid rows, everything in memory after a warm-up scan, one run per version:

IS JSON was about 2.6 times as fast as the jsonb check, and gave the wrong answer for jsonb on the three inputs above. A few hundred milliseconds to list the bad rows in a million is cheap next to one failed ALTER per bad row, as the next section shows.

Why does ALTER COLUMN TYPE jsonb fail, and what does a failure cost?

alter table legacy alter column payload type jsonb using payload::jsonb stops at the first row it cannot cast, and undoes everything:

ERROR:  22P02: invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: {'...

Afterwards the column was still text with all nine rows. In a transaction that first updated a row and then ran the failing ALTER, the COMMIT ended as a ROLLBACK and the update was gone too. The message names no table, column or row id, only the start of the JSON. It is also only about the first invalid row the scan reached, here id 3; the rows with NaN, \u0000 and the empty string did not show up. Fixing and retrying one row at a time means one failed ALTER per bad row, and how long each one takes depends on where that row is.

A failure near the end of the table cost almost as much as a successful rewrite. The guard made a successful ALTER on 990,000 valid rows about 0.8 to 0.9 seconds slower, roughly 40 to 50%. The likely reason is that every row is parsed twice, once to check and once to cast; we did not measure that separately.

How do you cast text to jsonb without failing on bad rows?

To get NULL instead of an error, you put pg_input_is_valid in front of the cast. Where you put that check turned out to matter, and on PostgreSQL 18 one form became unreliable. All four variants use case when pg_input_is_valid(t, 'jsonb') then t::jsonb end:

“Works” means NULL for each invalid row. “Fails” means the 22P02 the function was written to avoid. Neither strict nor a SQL-standard RETURN body changed anything.

On PostgreSQL 18.6 the immutable SQL function failed on our nine-row table, with CONTEXT: ... SQL function "try_jsonb_sql" statement 1, where 16.15 and 17.11 returned nine rows. It did not always fail. In a new session it failed when the invalid value came in one of the function’s first five calls, and from the sixth call on it returned NULL, also in later queries in the same session. An ALTER on 100,000 rows whose first invalid row was id 100 succeeded with it. With plan_cache_mode = force_custom_plan it failed on 18 also when the invalid row came eighth, and still worked on 16 and 17. With force_generic_plan it always worked. Whether your migration stops therefore depends on what the connection did before.

That is a planner change. The PostgreSQL 18 release notes list it under the optimizer, not under the incompatibilities: “Improve SQL-language function plan caching” (release notes). The commit behind it says the old plans “could not be customized to specific values of the function’s parameters”. Our reading is that a custom plan puts the argument into the body as a constant, and the planner then evaluates the immutable cast before the case can skip it. The documentation warns about exactly this, that “the values of function arguments and local variables can be inserted into queries as constants for planning purposes” (§4.2.14). The five calls match the plan cache’s rule that “the first five executions are done with custom plans” (PREPARE). We infer that the same rule applies here and did not trace it in the source. Inlining fits what we saw: pg_input_is_valid is itself stable, and EXPLAIN VERBOSE showed the stable and volatile SQL functions written out as a case in the plan and the immutable one as a call, try_jsonb_sql(payload).

The variant that held up in every case we measured is PL/pgSQL. The same section of the documentation recommends it: “using an IF-THEN-ELSE statement to protect a risky computation is much safer than just nesting it in a CASE expression.”

create or replace function try_jsonb(t text) returns jsonb
language plpgsql immutable
as $$
begin
  if pg_input_is_valid(t, 'jsonb') then return t::jsonb; end if;
  return null;
end $$;

alter table legacy alter column payload type jsonb using try_jsonb(payload);

That ALTER succeeded on all three versions, and so did using case when pg_input_is_valid(payload, 'jsonb') then payload::jsonb end, the form in the figure above. The PL/pgSQL function is slower: in one rerun, on the 990,000-row table with the invalid row last, it took about 3.2 seconds on each version, against 2.6 for the case and 1.8 for the plain cast on the 990,000 valid rows in the figure. Both turn every invalid row into NULL, which is a decision about your data and not a fix. List the rows with pg_input_error_info first and decide per kind of error: a Python repr can be repaired, an empty string probably means NULL, and a \u0000 needs a choice about what the character should become.

Which mistakes do not raise the error at all?

The ones that happen to be valid JSON:

• An empty array from a driver became {}, an empty object, in node-postgres and psycopg.
• A double-encoded document, "{\"a\": 1}", is a valid JSON string. ->> 'a' on it returned NULL without an error; (payload #>> '{}')::jsonb ->> 'a' unwrapped it and returned 1.
• Duplicate keys went into jsonb with only the last value kept.
• NaN inside a JavaScript object became null through JSON.stringify.
• NaN and Infinity from SQL: to_jsonb('NaN'::numeric) returned the JSON string "NaN", and to_jsonb('Infinity'::float8) the string "Infinity". No error, but a number has become a string.

What does this error look like in your driver?

For a literal in the SQL, SQLSTATE 22P02 is the same in every client; the exception and the wording around the message are not. We raised this error from twenty client versions on PostgreSQL 17.11, with the same result on 18.6; the table shows sixteen. The … is PostgreSQL’s own text, here invalid input syntax for type json, and <sql> your statement.

The Hibernate lines are the first of two log lines; the second carries the message, at ERROR on 6.6 and WARN on 7.4. Prisma 6’s message runs on over the DETAIL line, hence no closing backtick in its row. lib/pq 1.12 adds the position in your statement (at column N) and the code; 1.10 printed neither.

With the same text bound as a string parameter (VALUES (2, $1)), fourteen clients got the same 22P02. pgJDBC, JdbcTemplate and Hibernate got 42804 instead, column "payload" is of type jsonb but expression is of type character varying, and Prisma 6 the same with text; JdbcTemplate then shows only bad SQL grammar on its first line, with the message on the Caused by: line: the error you search for then is column is of type jsonb but expression is of type, and the invalid JSON is only found once that is fixed.

Which field holds the SQLSTATE per driver is in our overview.

Reproduce it

Two files: setup.sql holds the 38 inputs, the casts, the checks, the migration and the safe casts, and with -v bench=1 the million-row timings; run.sh starts a container per PostgreSQL version, runs it, runs the node-postgres and psycopg tests against the newest version, and removes everything again. It needs Docker; BENCH=0 ./run.sh skips the million rows. The output lands in a results folder next to the script.

What we did not measure

Databases with an encoding other than UTF8, where the documentation says jsonb rejects more. How Drizzle, TypeORM and the ORM layers of Prisma, SQLAlchemy and Django serialise a value before it reaches the driver; the driver table covers raw SQL only. What psycopg put on the wire. COPY beyond the one message above, including ON_ERROR in PostgreSQL 17, json_populate_record, and JSON_QUERY with ERROR ON ERROR. Where in the source PostgreSQL 18 decides between a custom and a generic plan for a SQL function, and whether the five calls are always five. The lock an ALTER ... TYPE jsonb takes, and for how long. For the timings: one run per version, plus one rerun that reproduced them within about 4% apart from the early failure, no cold cache, no large documents and no concurrent load. The cost of the PL/pgSQL function comes from that single rerun.

Sources: PostgreSQL 17 documentation on JSON types, on pg_input_is_valid and pg_input_error_info, on IS JSON, on expression evaluation rules (§4.2.14) and on PREPARE; the PostgreSQL 18 release notes and commit 0dca5d68d; node-postgres documentation on queries and types, all read on 12 November 2026. All measurements are our own, on PostgreSQL 16.15, 17.11 and 18.6 in throwaway containers, with node-postgres 8.23.1, Kysely 0.29.8 and psycopg 3.3.8 against 18.6, on 12 November 2026, with a rerun the same day. Driver table on 17.11 and 18.6, versions in the README, on 15 November 2026.