PostgreSQL 2203E Error: Causes and Solutions Complete Guide

-- Problematic: too many key-value pairs in one call
SELECT json_build_object(
    'col1', col1,
    'col2', col2,
    -- ... hundreds more
    'col300', col300
)
FROM huge_table;

-- Fix: split and merge using jsonb || operator
SELECT
    jsonb_build_object('col1', col1, 'col2', col2)
    ||
    jsonb_build_object('col3', col3, 'col4', col4)
AS merged_result
FROM huge_table;

-- Alternative: use row_to_json for full row conversion
SELECT row_to_json(t) FROM huge_table t;

-- Problematic: no limit on aggregated rows
SELECT
    user_id,
    json_object_agg(event_key, event_value) AS all_events
FROM user_events
GROUP BY user_id;

-- Fix 1: filter before aggregating
SELECT
    user_id,
    json_object_agg(event_key, event_value) AS recent_events
FROM (
    SELECT user_id, event_key, event_value
    FROM user_events
    WHERE created_at >= NOW() - INTERVAL '30 days'
) recent
GROUP BY user_id;

-- Fix 2: use jsonb_agg with array structure instead
SELECT
    user_id,
    jsonb_agg(
        jsonb_build_object('key', event_key, 'value', event_value)
    ) AS events_array
FROM user_events
GROUP BY user_id;

-- Check member count before processing
SELECT
    id,
    (SELECT COUNT(*) FROM jsonb_object_keys(raw_payload)) AS member_count
FROM incoming_data
WHERE (SELECT COUNT(*) FROM jsonb_object_keys(raw_payload)) > 500;

-- Safe extraction: pick only allowed keys
SELECT
    id,
    (
        SELECT jsonb_object_agg(key, value)
        FROM jsonb_each(raw_payload)
        WHERE key = ANY(ARRAY['name', 'email', 'status'])
    ) AS safe_json
FROM incoming_data;

-- Add a trigger to enforce limits on insert
CREATE OR REPLACE FUNCTION enforce_json_member_limit()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(*) FROM jsonb_object_keys(NEW.payload)) > 500 THEN
        RAISE EXCEPTION '2203E: JSON object member limit exceeded';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_json_limit
BEFORE INSERT OR UPDATE ON incoming_data
FOR EACH ROW EXECUTE FUNCTION enforce_json_member_limit();

-- Instead of: {"2024-01-01": 10, "2024-01-02": 20, ...}
-- Do this:
SELECT jsonb_build_object(
    'year', 2024,
    'entries', jsonb_agg(
        jsonb_build_object('date', sale_date, 'amount', amount)
    )
)
FROM daily_sales
GROUP BY EXTRACT(YEAR FROM sale_date);

SELECT
    MAX(cnt) AS max_members,
    AVG(cnt)::NUMERIC(10,1) AS avg_members,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY cnt) AS p99_members
FROM (
    SELECT COUNT(*) AS cnt
    FROM your_table t,
    jsonb_object_keys(t.json_col)
    GROUP BY t.id
) s;