invalid input syntax for type json when a column mixes JSON and plain text
ERROR: invalid input syntax for type jsonShort answer
PostgreSQL checks every value you cast to json or jsonb and stops the whole statement at the first one that is not valid JSON. A column that mixes JSON and plain strings fails that way. Find the bad rows with pg_input_is_valid() on PostgreSQL 16 or newer, filter them out in a CASE, or trap the error in a small PL/pgSQL function.
Fix it fast
1Find out which cause you have
List the rows whose text is not valid JSON. On PostgreSQL 15 or older this function does not exist, so use the PL/pgSQL helper in the second cause.
SELECT id, payload FROM events WHERE NOT pg_input_is_valid(payload, 'json');2Match what you see, then jump to its fix
- 1
The cast works on a few rows but the full query fails; a LIMIT hides the errorSome rows hold plain text, not JSONFix - 2
An error saying the function pg_input_is_valid does not existPostgreSQL 15 or older has no pg_input_is_valid()Fix - 3
The same value passes ::json and fails ::jsonb, often with a Unicode escape or a very large numberThe text is valid for json but rejected by jsonbFix
- 1
What does the error look like?
ERROR: invalid input syntax for type json
Cause 1 of 3
Some rows hold plain text, not JSON
1Check
The manual says the JSON types enforce that every stored value is valid JSON. The statement cast fails on the first bad value, so one stray word in millions of rows breaks it.
SELECT count(*) FILTER (WHERE pg_input_is_valid(payload, 'json')) AS valid_rows,
count(*) FILTER (WHERE NOT pg_input_is_valid(payload, 'json')) AS bad_rows
FROM events;2Fix
Cast only the valid rows. The CASE guards the cast, so the invalid text becomes NULL instead of raising an error. The manual says pg_input_is_valid() only works as intended when the type's input function reports bad input softly; otherwise bad input aborts the statement. Test it on one bad value first, and if it raises an error, use the function from the next cause.
SELECT id,
CASE WHEN pg_input_is_valid(payload, 'json')
THEN payload::json END AS payload_json
FROM events;3Verify
Run the query without LIMIT and confirm it finishes.
SELECT count(*) FROM events WHERE pg_input_is_valid(payload, 'json');Confirmed by postgresql.org
Cause 2 of 3
PostgreSQL 15 or older has no pg_input_is_valid()
1Check
The functions pg_input_is_valid() and pg_input_error_info() were added in the PostgreSQL 16 release notes. Older servers need an error trap.
SHOW server_version;2Fix
Write a function with a BEGIN ... EXCEPTION block that returns NULL when the cast fails. The manual warns that a block with EXCEPTION costs more to enter, so use it for clean-up jobs, not hot queries.
CREATE FUNCTION try_json(txt text) RETURNS json
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
RETURN txt::json;
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END;
$$;
SELECT id, try_json(payload) FROM events;3Verify
Run the SELECT over the whole table.
SELECT count(*) FROM events WHERE try_json(payload) IS NULL;Confirmed by postgresql.org
Cause 3 of 3
The text is valid for json but rejected by jsonb
1Check
The manual says jsonb is stricter: it rejects the \u0000 escape and Unicode escapes that cannot be represented in the database encoding, and numbers outside the range of numeric, while json accepts them.
SELECT pg_input_is_valid(payload, 'json') AS as_json,
pg_input_is_valid(payload, 'jsonb') AS as_jsonb
FROM events
WHERE pg_input_is_valid(payload, 'json') AND NOT pg_input_is_valid(payload, 'jsonb');2Fix
Keep those rows as json, or clean the offending escape or number at the source before storing as jsonb.
ALTER TABLE events ADD COLUMN payload_json json;
UPDATE events SET payload_json = payload::json WHERE pg_input_is_valid(payload, 'json');3Verify
Count the rows that now have a stored json value.
SELECT count(payload_json) FROM events;Confirmed by postgresql.org
How do I stop it coming back?
- Store JSON in a json or jsonb column from the start, so bad input is rejected at insert time, not at a later cast.
- Validate JSON in the application before it reaches the database.
- Put a CHECK constraint with pg_input_is_valid() on text columns that must hold JSON (PostgreSQL 16 and newer).
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix