From: Tom Lane <tgl@sss.pgh.pa.us>
To: ewherrmann@gmail.com
cc: pgsql-bugs@lists.postgresql.org
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>
References: <18356-a811d014209fd19c@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Wed, 21 Feb 2024 19:13:24 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <123108.1708546678.1@sss.pgh.pa.us>
Date: Wed, 21 Feb 2024 15:17:58 -0500
Message-ID: <123109.1708546678@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/123109.1708546678%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> - 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"'. 

Could you provide an actually self-contained example?  People
have other things to do than guess about the data and table details
needed to reproduce this.

			regards, tom lane