I tried unsuccessfully to recreate the error with this self-contained example:
SELECT mytdata.key, mytdata.value::float::integer FROM (VALUES('{"key_figure": "volume", "w01": 0, "w02": 3, "w03": 2.0}'::jsonb)) AS myt (weekdata), jsonb_each_text(myt.weekdata) AS mytdata -- WHERE mytdata.key IN ('w01', 'w02', 'w03') WHERE mytdata.key IN ( SELECT myk.key from (VALUES('w01'), ('w02'), ('w03')) AS myk (key) )
This example does not cause the same error and returns results as expected. If it has to do with the execution plan as Laurenz suggested, do you have a suggestion on how to better create a self-contained example where it would behave more like an actual table select to get the desired list of keys?
Laurenz Albe <laurenz.albe@cybertec.at> writes: > On Wed, 2024-02-21 at 19:13 +0000, PG Bug reporting form wrote: >> However as of v16, this same statement causes the error: 'invalid input >> syntax for type double precision: "Volume"'.
> That's not a bug. PostgreSQL just chose a different execution plan > that happened to evaluate the SELECT list elements before filtering out > the rows.
I'm not sure if it's a bug or not. I think we do promise that WHERE filtering happens before SELECT list evaluation, and this query looks like it ought to meet that condition. But without a self-contained example it's hard to be entirely sure what's happening.
