JSON Errors / PostgreSQL
ERROR: invalid input syntax for type jsonHow to Fix: ERROR: invalid input syntax for type json
PostgreSQL tried to cast a string to json/jsonb and the string is not valid JSON. The DETAIL line tells you exactly what it found ("Token "x" is invalid", "Expected string or "}"...") and at which character.
The classic confusion is quoting: SQL string literals use single quotes on the outside, but the JSON inside them still requires double quotes - '{"a": 1}' is right, '{''a'': 1}' is not.
Common causes
1. Single-quoted "JSON" inside the SQL literal
JSON syntax rules apply inside the SQL string.
INSERT INTO events (payload) VALUES ('{''type'': ''login''}');INSERT INTO events (payload) VALUES ('{"type": "login"}');2. Python dict string instead of JSON
Passing str(dict) - {'type': 'login'} - through a driver parameter. Serialize with json.dumps first (or use psycopg's Json adapter).
3. Double-escaped JSON from application code
JSON stringified twice arrives as "{\"type\":...}" - a JSON string containing JSON, not a JSON object. Postgres casts it to a json string value or rejects it depending on context.
How to fix it
import json, psycopg
payload = {"type": "login", "user_id": 42}
# either serialize explicitly:
cur.execute("INSERT INTO events (payload) VALUES (%s)", (json.dumps(payload),))
# or let the driver adapt the dict:
from psycopg.types.json import Json
cur.execute("INSERT INTO events (payload) VALUES (%s)", (Json(payload),))-- Postgres 16+: test castability without an exception
SELECT payload_text FROM staging
WHERE payload_text IS NOT JSON;
-- older versions: attempt the cast in a controlled scope or fix upstreamFrequently asked questions
json or jsonb - does the choice affect this error?
Both validate input the same way, so no. jsonb additionally normalizes (dedupes keys keeping the last, drops whitespace, reorders) and supports indexing - it is the right default. json stores the exact text and preserves duplicates/order for the rare cases that need it.
Why does the same insert work from psql but fail from my app?
Your shell/psql session and your driver escape differently. In app code the usual culprit is double serialization (an ORM serializing an already-serialized string) - log the exact parameter value the driver sends and check whether it starts with a quote character.
Escaped unicode like \u0000 fails on jsonb. Why?
jsonb rejects the null byte escape \u0000 because Postgres text cannot contain NUL - "unsupported Unicode escape sequence". Strip or replace \u0000 in the application before insert; plain json columns accept it but comparisons on it will fail later.
Last updated:
Related JSON errors
You might also need
Free and in-browser, like everything on JSON Console. Browse all tools →