Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

invalid input syntax for type json when a column mixes JSON and plain text

ErrorERROR: invalid input syntax for type json

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

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

Fix it fast

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

    1. 1The cast works on a few rows but the full query fails; a LIMIT hides the errorSome rows hold plain text, not JSONFix
    2. 2An error saying the function pg_input_is_valid does not existPostgreSQL 15 or older has no pg_input_is_valid()Fix
    3. 3The 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

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

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