From: Amit Langote <amitlan@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression
Date: Mon, 06 Jul 2026 23:46:32 +0000
Message-ID: <E1wgt1F-001f1S-06@gemulon.postgresql.org> (raw)
Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expressions
transformJsonBehavior() coerced an ON EMPTY / ON ERROR DEFAULT
expression only when its type differed from the RETURNING type's OID.
When the base type matched but the RETURNING type carried a type
modifier (e.g. numeric(4,1) or varchar(3)), the coercion that enforces
the typmod was skipped, so the DEFAULT value could violate the
declared type:
SELECT JSON_VALUE(jsonb '{}', '$.a'
RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);
returned 99999.999, which 99999.999::numeric(4,1) would reject; the
value could even be stored into a numeric(4,1) column, as later
coercions trust its already-correct type label.
Fix by also coercing when the RETURNING type has a typmod, except for
a NULL constant. coerce_to_target_type() is a no-op when the typmod
already matches. The matching-OID short-circuit dates to 74c96699be3.
Reported-by: Ewan Young <kdbase.hack@gmail.com>
Author: Ewan Young <kdbase.hack@gmail.com>
Discussion: https://postgr.es/m/CAON2xHPO9f4cAmyGn1mQ=VqoS7wN5rz4yOiqudxX78zninZpCw@mail.gmail.com
Backpatch-through: 17
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/4c75cc786301886145bc1a450977cbd024814ef5
Modified Files
--------------
src/backend/parser/parse_expr.c | 11 ++++++++++-
src/test/regress/expected/sqljson_jsontable.out | 16 ++++++++++++++++
src/test/regress/expected/sqljson_queryfuncs.out | 21 +++++++++++++++++++++
src/test/regress/sql/sqljson_jsontable.sql | 9 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 8 ++++++++
5 files changed, 64 insertions(+), 1 deletion(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: amitlan@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression
In-Reply-To: <E1wgt1F-001f1S-06@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox