Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Importing a CSV into a json column fails on a single quote

ErrorERROR: invalid input syntax for type json

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1The cells look like {'name': 'Ana', 'active': True}, a Python-style dict.The values use single quotes instead of JSON double quotesFix
    2. 2Fields 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. 3You are building INSERT statements by hand and the string includes O'Brien.An apostrophe inside a JSON string, written in SQL, is not doubledFix

What does the error look like?

  • ERROR: invalid input syntax for type json
  • DETAIL: 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

Did this fix it?

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.csv

2Fix

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.