DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

PostgreSQL 22030 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22030: Duplicate JSON Object Key Value

PostgreSQL error code 22030 (duplicate_json_object_key_value) is raised when a JSON object contains two or more entries with the same key, specifically when inserting or casting data into a jsonb column. Unlike the json type (which stores text as-is), jsonb parses and validates JSON at write time, making it intolerant of duplicate keys. This error is especially common in data pipeline workflows where unvalidated external JSON is written directly to the database.

Top 3 Causes

1. Inserting Duplicate-Key JSON into a jsonb Column

The most straightforward cause: passing a JSON string with repeated keys to a jsonb column triggers 22030 immediately at parse time.

-- This fails with ERROR 22030
SELECT '{"name": "Alice", "name": "Bob"}'::jsonb;

-- Safe workaround: deduplicate via json_each + jsonb_object_agg
SELECT jsonb_object_agg(key, value)
FROM json_each('{"name": "Alice", "name": "Bob"}'::json);
-- Result: {"name": "Bob"}  (last-value-wins)
Enter fullscreen mode Exit fullscreen mode

2. Duplicate Keys in jsonb_build_object() Calls

When constructing JSON dynamically in SQL, it's easy to accidentally reference the same key twice — especially during refactoring or when merging multiple data sources.

-- Broken: duplicate "email" key
SELECT jsonb_build_object(
  'user_id', u.id,
  'email',   u.email,
  'email',   u.secondary_email  -- ERROR 22030
)
FROM users u WHERE u.id = 1;

-- Fixed: use distinct keys
SELECT jsonb_build_object(
  'user_id',         u.id,
  'primary_email',   u.email,
  'secondary_email', u.secondary_email
)
FROM users u WHERE u.id = 1;

-- Merging two jsonb objects safely (later key wins)
SELECT
  '{"role": "admin", "email": "old@example.com"}'::jsonb
  || '{"email": "new@example.com"}'::jsonb;
-- Result: {"role": "admin", "email": "new@example.com"}
Enter fullscreen mode Exit fullscreen mode

3. Inbound Data from External APIs or ETL Pipelines

Third-party APIs and message queues sometimes emit JSON with duplicate keys (either by bug or by design). If your pipeline writes this data directly to a jsonb column without sanitization, batch jobs will fail unpredictably in production.

-- A reusable sanitizer function for ETL pipelines
CREATE OR REPLACE FUNCTION safe_to_jsonb(input_json TEXT)
RETURNS jsonb
LANGUAGE plpgsql AS $$
BEGIN
  RETURN (
    SELECT jsonb_object_agg(key, value)
    FROM json_each(input_json::json)
  );
EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'Could not parse JSON: %', input_json;
    RETURN NULL;
END;
$$;

-- Use it in bulk inserts
INSERT INTO events (payload)
SELECT safe_to_jsonb(raw_payload)
FROM staging_events
WHERE raw_payload IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario Fix
One-off bad input Cast to json first, then use jsonb_object_agg to deduplicate
Dynamic SQL build Audit all jsonb_build_object() calls for repeated keys
External data Apply safe_to_jsonb() wrapper before any jsonb write
Merge two objects Use the `

Prevention Tips

  1. Use a two-stage ingestion pattern. Accept external JSON into a {% raw %}json-typed staging table first (which permits duplicate keys), validate and deduplicate it, then promote clean records to your jsonb production table. This keeps your pipeline resilient without swallowing bad data silently. 
-- Staging accepts anything
CREATE TABLE staging_events (raw json, received_at timestamptz DEFAULT now());

-- Production enforces clean jsonb
CREATE TABLE events (payload jsonb NOT NULL, created_at timestamptz DEFAULT now());
Enter fullscreen mode Exit fullscreen mode
  1. Validate at the application layer before hitting the database. Configure your JSON serializer to reject duplicate keys (e.g., FAIL_ON_DUPLICATE_KEYS in Jackson for Java, object_pairs_hook in Python's json module). Catching duplicates before the network round-trip is always faster than handling a database exception, and it keeps your error logs meaningful.

Related Errors

  • 22032 invalid_json_text — Malformed JSON syntax; often appears alongside 22030 during data ingestion.
  • 22P02 invalid_text_representation — Failed cast from text to JSON/JSONB; handle together with 22030 in EXCEPTION blocks.
  • 23505 unique_violation — Row-level uniqueness conflict; distinct from 22030 but sometimes confused when working with JSON-keyed indexes.

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

Top comments (0)