PostgreSQL raises this error when JSON object constructors with UNIQUE constraints detect duplicate keys. Most often occurs with json() constructor, JSON aggregate functions, or when merging JSON objects.
JSON objects are key-value pairs where each key must be unique. In PostgreSQL 15+, the json() constructor with WITH UNIQUE KEYS option enforces this constraint. When you attempt to create or manipulate JSON data containing duplicate keys, PostgreSQL (error code 22030) signals that the JSON structure is invalid. For jsonb types, PostgreSQL silently keeps the last value; for json type with UNIQUE validation, it raises an error. This is a data integrity safeguard to prevent ambiguous JSON structures.
Run a test query to confirm duplicate keys exist in your data:
SELECT json('{"a": 12, "a": 23}' WITH UNIQUE KEYS);
-- ERROR: duplicate JSON object key valueWithout the WITH UNIQUE KEYS clause, PostgreSQL accepts the duplicate (last value wins for jsonb):
If duplicate keys should be silently handled, switch to WITHOUT UNIQUE KEYS or use jsonb which keeps the last value:
SELECT json('{"a": 12, "a": 23}' WITHOUT UNIQUE KEYS);
-- Result: {"a": 23}
SELECT jsonb('{"a": 12, "a": 23}');
-- Result: {"a": 23}If building JSON from SQL queries, ensure keys are unique before aggregating:
SELECT json_object_agg_unique(key, value)
FROM (
SELECT DISTINCT ON (key) key, value
FROM my_table
ORDER BY key, created_at DESC -- Keep last value
) AS deduped;If receiving JSON from external sources, deduplicate in your application before passing to PostgreSQL:
// JavaScript/TypeScript example
const deduped = Object.fromEntries(
Object.entries(jsonData).reverse()
);This keeps the first occurrence (due to reverse).
When building JSON incrementally, use jsonb_set which prevents duplicates:
SELECT jsonb_set('{}', '{a}', '12')
|| jsonb_set('{}', '{a}', '23');
-- Result: {"a": 23} (last wins)PostgreSQL 16+ introduced a bug in the uniqueness check for JSON object constructors (fixed in 16.1), which could cause both false negatives (producing JSON with duplicate keys) and false positives (rejecting valid JSON). If you encounter inconsistent behavior with duplicate detection, ensure you are running PostgreSQL 16.1 or later. The json type (text-based) and jsonb type (binary) behave differently: jsonb only stores the last value for duplicate keys without raising an error, while the json() constructor with WITH UNIQUE KEYS enforces uniqueness at parse time. For most applications, jsonb is recommended for performance and automatic deduplication benefits.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL