- 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 )