From: | Ed Herrmann <ewherrmann(at)gmail(dot)com> |
---|---|
To: | ewherrmann(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select |
Date: | 2024-02-21 19:19:15 |
Message-ID: | CAOfL=zr_TEdAeBnCjoONdH5J3zPa=6FtB_ORHGOGoNna7vghuA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
There was a typo in my original examples:
SELECT id, mytdata.key, mytdata.value::integer
should be:
SELECT id, mytdata.key, mytdata.value::float::integer
I don't think it matters for getting an error, but it would matter given
the specific error about double precision.
On Wed, Feb 21, 2024 at 2:14 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18356
> Logged by: Ed Herrmann
> Email address: ewherrmann(at)gmail(dot)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 )
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2024-02-21 19:19:49 | Re: DSA_ALLOC_NO_OOM doesn't work |
Previous Message | PG Bug reporting form | 2024-02-21 19:13:24 | BUG #18356: Casting values from jsonb_each_text does not respect WHERE filter with sub select |