[SOLVED] postgresql cannot cast text[] to uuid

postgresql cannot cast text[] to uuid

I have an array like this:

["f383224c-aa95-40c9-a4e4-fa41747f49c5","c783234c-aa95-40c9-a4e4-fa41747f49c6"]

And I want to select with any but I got an error cannot cast text[] to uuid

SELECT c.name FROM cars c 

LEFT JOIN location_orders_cars loc ON loc.car_id = c.id 

WHERE loc.user_id = ANY($1::uuid) AND loc.location_orders_id = $2 AND loc.is_back = NULL 

LIMIT 10;

$1 = array of UUID above

$2 = 355

what I am doing wrong here ?

Solution

You have a couple problems other than the error your getting.

First off the predicate loc.is_back = NULL will never be true. You cannot use logical/arithmetic operators with null; they will always return null, even the predicate null = null is not True; you need to use this case you need loc.is_back IS NULL.

Second, in Postgres double quotes indicate identifiers (table name, column name, etc). Your array values need to have single quotes.

Now the main event: You can use ANY with an array, but not if you need to cast data types. You can either pass a the uuids as uuid[]. If you cannot do that then you need to unnest the array and cast the individual values. Something like (see demo)

where loc.user_id = any(select u::uuid 
    from unnest(array['f383224c-aa95-40c9-a4e4-fa41747f49c5','c783234c-aa95-40c9-a4e4-fa41747f49c6']) a(u)
)