From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: ewherrmann@gmail.com
Subject: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
Date: Wed, 21 Feb 2024 19:13:24 +0000
Message-ID: <18356-a811d014209fd19c@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18356
Logged by: Ed Herrmann
Email address: ewherrmann@gmail.com
PostgreSQL version: 16.0
Operating system: Linux, MacOS
Description:
- Prior to v16, this statement would work for casting values of a k/v jsonb
pair to an integer:
SELECT id, mytdata.key, mytdata.value::integer
FROM my_table as myt,
jsonb_each_text(myt.data) as mytdata
WHERE mytdata.key IN ( SELECT key from week_key_table
)
- Where week_key_table stores keys such as "week01", "week02", and
"week03".
- And where the jsonb has some keys with alphanumeric values and some keys
with numeric values, such as: { "key_figure": "Volume", "week01": "0",
"week02": "0", "week03": "0"}
However as of v16, this same statement causes the error: 'invalid input
syntax for type double precision: "Volume"'.
If the keys are specified directly, it will still work as normal, but thows
the casting error when the keys are being selected from another table.
Works:
SELECT id, mytdata.key, mytdata.value::integer
FROM my_table as myt,
jsonb_each_text(myt.data) as mytdata
WHERE mytdata.key IN ( "week01", "week02", "week03" )
Casting Error:
SELECT id, mytdata.key, mytdata.value::integer
FROM my_table as myt,
jsonb_each_text(myt.data) as mytdata
WHERE mytdata.key IN ( SELECT key from week_key_table )
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-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, ewherrmann@gmail.com
Subject: Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select
In-Reply-To: <18356-a811d014209fd19c@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