Postres: "No operator matches the given name and argument types. You might need to add explicit type casts."

Asked Aug 11 '21 at 15:34

Active Aug 11 '21 at 15:34

Viewed 2,838 times

I'm trying to convert a JSON integer array to a postgres array for doing a WHERE clause, but I can't get it right. Tried casting to the array and to the whole subquery but I keep getting:

"LINE 1: select id from story where id in (SELECT ARRAY(SELECT jsonb_...
No operator matches the given name and argument types. You might need to add explicit type casts."

my query:

SELECT id FROM story WHERE id IN (SELECT ARRAY(SELECT jsonb_array_elements('[1000]'))::int[]);

Could please someone help? Thanks!

asked Aug 11 '21 at 15:34

Edmond Tamas

3,148
9
44
89

There are some details of `jsonb_array_elements` that you appear to have missed. Read [here](https://www.postgresql.org/docs/9.5/functions-json.html). – Robert Harvey Aug 11 '21 at 15:37
In particular, a cast appears to be required here. See [this Stack Overflow post](https://stackoverflow.com/questions/37686187). – Robert Harvey Aug 11 '21 at 15:40
thanks both, I'm browsing the docs for about 2 hours now, tried casting to text than in like in @RobertHarvey's link, still getting the same error. – Edmond Tamas Aug 11 '21 at 15:46
Try the contains operator: `select id from story where '[1000]'::jsonb @> id::text::jsonb;` – Mike Organek Aug 11 '21 at 16:07
`select '1000'::jsonb in (SELECT jsonb_array_elements('[1000]')); t` – Adrian Klaver Aug 11 '21 at 21:13

Postres: "No operator matches the given name and argument types. You might need to add explicit type casts."

0 Answers0