PostgreSQL 22032 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22032: invalid json text

PostgreSQL error code 22032 (invalid_json_text) is raised when the database engine encounters a string that cannot be parsed as valid JSON while inserting into a json/jsonb column or calling a JSON-related function. This error strictly enforces the JSON specification (RFC 7159), meaning even minor formatting issues — like single quotes or trailing commas — will trigger it. It is one of the most common data-ingestion errors in modern PostgreSQL applications that deal with semi-structured data.

Top 3 Causes

1. Malformed JSON Syntax (Wrong Quotes, Trailing Commas, Unbalanced Brackets)

JSON requires double quotes for keys and string values. Single quotes, trailing commas, or mismatched brackets are all invalid.

-- ❌ Causes ERROR 22032: single quotes, trailing comma
INSERT INTO logs (data) VALUES ('{"event": ''click'', }');

-- ✅ Correct syntax
INSERT INTO logs (data) VALUES ('{"event": "click"}');

-- ✅ Test before inserting using a DO block
DO $$
DECLARE
  v_input TEXT := '{"user": "alice"}';
BEGIN
  PERFORM v_input::jsonb;
  RAISE NOTICE 'JSON is valid';
EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'Invalid JSON: %', SQLERRM;
END;
$$;

2. Null Bytes or Illegal Control Characters Inside the String

Invisible characters such as null bytes (\u0000) or ASCII control characters (0x00–0x1F) embedded in JSON strings will cause the parser to fail. This commonly happens when data originates from legacy systems, binary file streams, or poorly encoded API responses.

-- ❌ Null byte causes invalid json text error
INSERT INTO events (payload)
VALUES (('{"name": "test' || chr(0) || '"}')::jsonb);

-- ✅ Strip null bytes and control characters before casting
INSERT INTO events (payload)
SELECT regexp_replace(
         replace(raw_input, chr(0), ''),
         '[\x01-\x1F\x7F]', '', 'g'
       )::jsonb
FROM staging_table;

-- ✅ Reusable safe conversion function
CREATE OR REPLACE FUNCTION safe_jsonb(input TEXT)
RETURNS JSONB AS $$
BEGIN
  RETURN regexp_replace(
    replace(input, chr(0), ''),
    '[\x01-\x1F\x7F]', '', 'g'
  )::jsonb;
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

3. Empty Strings or Truncated JSON Fragments

An empty string ('') is not valid JSON. Partial JSON fragments like {, [1,2,, or "unterminated are also rejected. This often happens when application-side serialization fails silently and sends an incomplete payload to the database.

-- ❌ Empty string is not valid JSON
INSERT INTO events (payload) VALUES ('')::jsonb;
-- ERROR: invalid input syntax for type json

-- ✅ Filter out invalid rows before bulk insert
INSERT INTO events (payload)
SELECT raw_json::jsonb
FROM staging_table
WHERE raw_json IS NOT NULL
  AND length(trim(raw_json)) > 2
  AND (
    (left(trim(raw_json),1) = '{' AND right(trim(raw_json),1) = '}')
    OR
    (left(trim(raw_json),1) = '[' AND right(trim(raw_json),1) = ']')
  );

Quick Fix Solutions

-- Identify bad rows in a staging (TEXT) table
SELECT id, raw_json
FROM staging_table
WHERE raw_json IS NOT NULL
  AND safe_jsonb(raw_json) IS NULL;  -- using function defined above

-- Quarantine invalid rows for later review
CREATE TABLE json_parse_errors (
  id          SERIAL PRIMARY KEY,
  raw_input   TEXT,
  error_msg   TEXT,
  captured_at TIMESTAMPTZ DEFAULT now()
);

-- Bulk load with error isolation
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT id, raw_json FROM staging_table LOOP
    BEGIN
      INSERT INTO events (payload) VALUES (r.raw_json::jsonb);
    EXCEPTION WHEN OTHERS THEN
      INSERT INTO json_parse_errors (raw_input, error_msg)
      VALUES (r.raw_json, SQLERRM);
    END;
  END LOOP;
END;
$$;

Prevention Tips

Use jsonb columns and let PostgreSQL enforce validity automatically. Unlike TEXT, a jsonb column rejects invalid JSON at the storage layer — no extra constraints needed.

-- Best practice: always use jsonb, not text, for JSON data
CREATE TABLE events (
  id      BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

Always serialize JSON through a trusted library in your application layer — never build JSON strings via manual concatenation. Use json.dumps() (Python), JSON.stringify() (JavaScript), or ObjectMapper (Java). Pair this with a staging-table ETL pattern where raw data lands as TEXT first, gets validated, and only then is promoted to the production JSONB column. This two-step approach preserves raw data for debugging while keeping your production tables clean.

Related Errors

22P02 (invalid_text_representation) — Broader type-cast failure; often appears alongside 22032 when non-JSON types are also misformatted.
22000 (data_exception) — Triggered by structural issues in JSON function arguments (e.g., bad path in jsonb_set).

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community