Dev & EngARTICLE

Invalid input syntax for type json in PostgreSQL: how to find the real cause

Invalid input syntax for type json in PostgreSQL: how to find the real cause
Image: Roberto Diniz
Article · Dev & Eng
Reading
7 min
Tables
1
Code
2
In short

A detailed benchmark shows that the most common error with json and jsonb columns in PostgreSQL hides different behaviors across drivers, versions, and even between json and jsonb in the same instance.

What the message actually says

The invalid input syntax for type json error appears whenever a text value is handed to a json or jsonb column and that text is not valid JSON for the PostgreSQL parser. A survey published on Planet PostgreSQL tested 38 different inputs against versions 16.15, 17.11, and 18.6 and arrived at an uncomfortable result: the message, the SQLSTATE (22P02, invalid_text_representation), and the DETAIL are identical across the three versions. Only the LOCATION line changes.

This means the message doesn't report the table, column, or row. A cast, an INSERT with a parameter, or an ALTER only show the start of the problematic text. Only COPY adds that information to the CONTEXT. In practice, anyone debugging this error in production is reading a string fragment, not a pointer to the data.

The DETAIL field points to the first token the parser rejected, which helps explain the type of problem but not its location. A Python dictionary's str(), for example, produces Token "'" is invalid., because the JSON parser doesn't accept single quotes. A PostgreSQL array such as {"a","b"} fails with Expected ":", but found ","., because the parser reads the curly braces as the start of an object.

Not every data error triggers the error

The benchmark clearly separates what fails from what goes unnoticed, and the second list is the more dangerous one for anyone designing a schema:

  • NaN, undefined, True (capitalized), and unquoted keys fail with 22P02 in both json and jsonb.
  • \u0000 inside a string is accepted by json but rejected by jsonb with a different code, 22P05 (untranslatable_character).
  • \ud800, an unpaired surrogate, follows the same rule: it passes in json, fails in jsonb.
  • A JSON document encoded twice, such as "{\"a\": 1}", is accepted by both types as a valid JSON string, not as an object.
  • Duplicate keys, such as {"a": 1, "a": 2}, are accepted by jsonb, which keeps only the last value.

The modeling takeaway here is direct: json checks "only syntactic correctness," while jsonb actually validates Unicode escapes, according to the documentation itself. A json column can store years of rows with \u0000 without ever complaining, until the day someone tries to migrate to jsonb or simply reads the value with ->>, which already fails with 22P05 even on a json column.

The driver is complicit in the error

A significant share of the cases the benchmark reproduced doesn't come from SQL, it comes from the driver. With node-postgres 8.23.1, a JavaScript array such as [1, 2] sent as parameter $1 to a jsonb column doesn't become JSON: it becomes a PostgreSQL array literal, {"1","2"}. The INSERT fails with 22P02 and Expected ":", but found ","., because the parser reads the curly braces as an object.

The trickiest case is the empty array. [] goes out as {}, which is valid JSON and is stored without error as an empty object, in both json and jsonb. Code that expects to read back an array gets {}. The fix, according to the benchmark, is to serialize the array yourself: JSON.stringify([1, 2]) is stored correctly as [1,2] in both column types, and also through libraries such as Kysely.

With psycopg 3.3.8 the behavior depends on the content of the Python list. [1, 2] fails with 42804 because the adapter types the value as smallint[]; ['a', 'b'] fails with 22P02; and [] silently becomes {}, just like in node-postgres. Passing a dict without a wrapper doesn't even get as far as generating a SQLSTATE, it's a client error. The benchmark's recommendation is to always use Jsonb(...) (or Json(...) for a json column) and never build the text with str(). Even so, the wrapper doesn't protect against NaN: a float('nan') inside the dictionary reaches PostgreSQL as the token NaN and brings down the INSERT.

Before the ALTER, find the bad rows

Converting a text column to jsonb with ALTER COLUMN ... TYPE jsonb USING payload::jsonb stops at the first invalid row and undoes everything, including an earlier UPDATE in the same transaction. Instead of finding this out in production, the benchmark recommends querying beforehand with pg_input_is_valid and pg_input_error_info:

sql
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;

This query doesn't stop at the first error: it lists all the problematic rows at once, with the SQLSTATE code and the DETAIL for each one. It's the difference between fixing one row per failed ALTER and fixing the whole batch in a single round of analysis.

One important caution: the SQL standard's IS JSON predicate looks equivalent to pg_input_is_valid(txt, 'json'), and it is, but it diverges from pg_input_is_valid(txt, 'jsonb') precisely in the three cases that matter for a migration, \u0000, {"a": "x\u0000y"}, and \ud800. Validating with IS JSON before an ALTER to jsonb lets those rows pass the check and still make the ALTER fail.

The cost of checking beforehand is small. In a test with one million rows (about 110 MB, 10,000 invalid rows), pg_input_is_valid(payload, 'jsonb') took between 301 and 311 ms across the three versions tested; IS JSON was about 2.6 times faster, but with the caveat of giving the wrong answer for jsonb in the three cases mentioned.

The price of an ALTER that really fails

The benchmark's numbers on PostgreSQL 18.6, on a table of about one million rows, show why it pays to validate beforehand:

ScenarioTime
Fails right at row 100 (second page)1.5 ms
Fails near the end (row 990,000)1,619 ms
Success, 990,000 valid rows, direct cast1,800 ms
Success, same batch, with pg_input_is_valid2,643 ms

A failure near the end of the table costs almost as much as a successful full rewrite, because PostgreSQL has already processed nearly all the rows before giving up. Prior validation makes a successful ALTER 40% to 50% more expensive, because each row is read twice, once to check and once to convert. It's an explicit trade-off: more execution time in exchange for a migration that doesn't vanish midway through.

The PostgreSQL 18 trap in the protective function

The most common way to avoid the error is to wrap the cast in a CASE: case when pg_input_is_valid(t, 'jsonb') then t::jsonb end. The benchmark tested four variants of this idea and found that, starting with PostgreSQL 18, one of them became unstable: the SQL function marked as immutable.

The reason is a change in plan caching, listed in the PostgreSQL 18 release notes under optimizer, not under incompatibilities: the improvement in SQL function plan caching lets the planner treat the argument as a constant inside a custom plan, and in that case it evaluates the immutable cast before the CASE can skip the bad row. This matches the documented rule that a function's first five executions use a custom plan; from the sixth execution on, a generic plan, without the problem.

The variant that remained reliable across the three versions tested was the PL/pgSQL function:

sql
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 $$;

Used in an ALTER COLUMN payload TYPE jsonb USING try_jsonb(payload), this function worked across all three versions tested. It's slower, about 3.2 seconds versus 2.6 for the direct CASE on the same batch of 990,000 valid rows, but it doesn't depend on how many times the session has already called the function before.

What this means for whoever decides the migration

Turning an invalid row into NULL is a decision about the data, not a fix. The benchmark recommends listing first with pg_input_error_info and deciding by error type: a Python dictionary's repr usually has an automatic fix, an empty string probably really should become NULL, and a \u0000 requires deciding what that character should have been. Running the ALTER directly on a large table without this prior diagnosis is betting that no row will cost the price of a full rewrite partway through.

Translated from the Brazilian Portuguese original · Read the original