Importing a CSV into a json column fails on a single quote
ERROR: invalid input syntax for type jsonShort answer
A json or jsonb column accepts only strict JSON: keys and strings in double quotes, lowercase true, false and null. A file written with single quotes, as Python prints dicts, is not JSON. In CSV the double quotes inside a field must also be doubled. Fix the file, then load it with COPY or psql's \copy in CSV format.
Fix it fast
1Find out which cause you have
Test one value from the file directly. If this cast fails, the problem is the JSON text, not the import tool.
SELECT '{"a": 1}'::jsonb; SELECT '{''a'': 1}'::jsonb;2Match what you see, then jump to its fix
- 1
The cells look like {'name': 'Ana', 'active': True}, a Python-style dict.The values use single quotes instead of JSON double quotesFix - 2
Fields contain double quotes and the import splits or mangles them.The file is read as plain text, so the CSV quoting of JSON is wrongFix - 3
You are building INSERT statements by hand and the string includes O'Brien.An apostrophe inside a JSON string, written in SQL, is not doubledFix
- 1
What does the error look like?
ERROR: invalid input syntax for type jsonDETAIL: Token "'" is invalid.
Cause 1 of 3
The values use single quotes instead of JSON double quotes
1Check
JSON requires double-quoted strings and keys, and only lowercase true and false. Single quotes in the data are not valid JSON, even though SQL itself wraps a literal in single quotes.
SELECT '{"active": TRUE}'::jsonb;2Fix
Regenerate the file with a real JSON encoder (json.dumps in Python, JSON.stringify in JavaScript) instead of printing the object.
import csv, json
rows = [{"name": "Ana", "active": True}]
with open("out.csv", "w", newline="") as f:
w = csv.writer(f)
for r in rows:
w.writerow([json.dumps(r)])3Verify
Cast a value from the new file.
SELECT '{"name": "Ana", "active": true}'::jsonb;Confirmed by postgresql.org
Cause 2 of 3
The file is read as plain text, so the CSV quoting of JSON is wrong
1Check
In CSV a field with the quote character is wrapped in quotes, and each inner quote is doubled by default. So {"a":1} must appear as "{""a"":1}". COPY without FORMAT csv uses the text format, which does not do this.
head -n 3 out.csv2Fix
Load the file explicitly as CSV. Use \copy from psql when the file is on your machine.
\copy events (payload) FROM 'out.csv' WITH (FORMAT csv)3Verify
Count and sample the rows.
SELECT count(*), min(payload::text) FROM events;Confirmed by postgresql.org
Cause 3 of 3
An apostrophe inside a JSON string, written in SQL, is not doubled
1Check
In a SQL literal that holds JSON, a single quote in the content must be written twice.
SELECT '{"name": "O'Brien"}'::jsonb;2Fix
Double the apostrophe in the literal, or load through COPY as above where SQL quoting does not apply.
SELECT '{"name": "O''Brien"}'::jsonb;3Verify
The value casts without error.
SELECT ('{"name": "O''Brien"}'::jsonb)->>'name';Confirmed by postgresql.org
How do I stop it coming back?
- Produce JSON with a library encoder, never with string formatting or repr.
- Import into a text column first when the source is unknown, then cast with a query that reports bad rows.
- Always name the format (FORMAT csv) and HEADER option in COPY.
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
invalid input syntax for type json when a column mixes JSON and plain text
ERROR: invalid input syntax for type json
PostgreSQL checks every value you cast to json or jsonb and stops the whole statement at the first one that is not valid JSON.
3 causes, each with a fixDatabases
Importing a CSV into PostgreSQL fails with Permission denied
Permission denied (CSV import through pgAdmin Import/Export)
COPY with a file name is read by the database server, not by pgAdmin or your computer.
3 causes, each with a fixDatabases
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 fix