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 attempts to parse a string as JSON but the input does not conform to valid JSON syntax. This commonly occurs during INSERT, UPDATE, or explicit casting operations targeting json or jsonb columns. Understanding the root causes and applying the right fixes will save significant debugging time in production environments.

Top 3 Causes

1. Malformed JSON Syntax (Missing Quotes, Trailing Commas, Unclosed Brackets)

The most frequent cause is JSON that looks correct but violates the standard — such as using single quotes instead of double quotes, leaving a trailing comma after the last element, or forgetting to close a bracket.

-- ❌ Single quotes on keys/values (JavaScript style, invalid JSON)
SELECT '{"name": ''Alice'', "active": true}'::jsonb;
-- ERROR: invalid input syntax for type json

-- ❌ Trailing comma after last element
SELECT '{"name": "Alice", "age": 30,}'::jsonb;
-- ERROR: invalid input syntax for type json

-- ✅ Valid JSON
SELECT '{"name": "Alice", "age": 30}'::jsonb;

-- ✅ Check validity before inserting (PostgreSQL 16+)
SELECT pg_input_is_valid('{"name": "Alice"}', 'jsonb');
-- Returns: true

2. Inserting Empty Strings or Plain Text into JSON Columns

A common mistake in ETL jobs or ORM-based applications is passing an empty string '' or a non-JSON text value (like "N/A" or "undefined") directly into a jsonb column. These are not valid JSON and will immediately trigger error 22032.

-- ❌ Empty string is not valid JSON
SELECT ''::jsonb;
-- ERROR: invalid input syntax for type json

-- ❌ Plain text is not valid JSON
SELECT 'hello world'::jsonb;
-- ERROR: invalid input syntax for type json

-- ✅ Use NULLIF to convert empty strings to NULL safely
INSERT INTO events (payload)
VALUES (NULLIF(trim(:input_value), '')::jsonb);

-- ✅ Wrap plain text as a JSON string using to_jsonb()
SELECT to_jsonb('hello world'::text);
-- Returns: "hello world"

-- ✅ Filter out invalid rows during batch migration
INSERT INTO new_events (id, data)
SELECT id, raw::jsonb
FROM staging_events
WHERE pg_input_is_valid(raw, 'jsonb') = true;

3. Unescaped Characters and Encoding Issues

Embedding unescaped double quotes or raw control characters (ASCII 0–31) inside a JSON string will cause the parser to fail. This frequently happens when JSON strings are built via string concatenation in application code rather than using a proper serialization library.

-- ❌ Unescaped double quotes inside JSON value
SELECT '{"message": "He said "Hello""}'::jsonb;
-- ERROR: invalid input syntax for type json

-- ✅ Use to_jsonb() for automatic escaping
SELECT to_jsonb('He said "Hello"'::text) AS safe_value;
-- Returns: "He said \"Hello\""

-- ✅ Use json_build_object() to safely compose JSON
SELECT json_build_object(
    'user', 'Alice',
    'note', 'She replied "OK"',
    'score', 42
);

-- ✅ Strip control characters before casting
SELECT regexp_replace(raw_col, '[[:cntrl:]]', '', 'g')::jsonb
FROM raw_data_table;

Quick Fix Solutions

Create a safe casting wrapper function to prevent unhandled exceptions:

CREATE OR REPLACE FUNCTION safe_to_jsonb(p_input TEXT)
RETURNS JSONB AS $$
BEGIN
    RETURN p_input::jsonb;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT safe_to_jsonb('{"ok": true}');   -- Returns JSONB value
SELECT safe_to_jsonb('bad input');       -- Returns NULL instead of error

Prevention Tips

1. Always use a JSON serialization library, never build JSON by string concatenation. In Python use json.dumps(), in Node.js use JSON.stringify(), and in Java use Jackson or Gson. This eliminates the majority of syntax errors before data ever reaches PostgreSQL.

2. Use jsonb columns with explicit defaults and add a CHECK constraint using pg_input_is_valid() for an extra layer of defense at the database level.

-- Safe table design with jsonb defaults and validation
CREATE TABLE user_settings (
    id       SERIAL PRIMARY KEY,
    user_id  INT NOT NULL,
    prefs    JSONB NOT NULL DEFAULT '{}',
    metadata JSONB NOT NULL DEFAULT '{}'
);

-- Add a constraint to block invalid JSON at DB level (PostgreSQL 16+)
ALTER TABLE user_settings
ADD CONSTRAINT chk_valid_prefs
CHECK (pg_input_is_valid(prefs::text, 'jsonb'));

For staging/ETL pipelines, always land raw data into a TEXT column first, validate with pg_input_is_valid(), log failures, and only then promote clean rows into JSONB columns.

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