I can’t find the right syntax for providing a list of values to epgsql:equery function to use in the sql where in clause. I googled and tried several variants, all of them failed. Some of my tries are below:
L = [1, 2, 3], epgsql:equery(Conn, "SELECT $1::integer[]", [L]), % this works epgsql:equery(Conn, "SELECT * FROM users WHERE id IN $1", [L]), % doesn't work epgsql:equery(Conn, "SELECT * FROM users WHERE id IN ($1)", [L]), % doesn't work epgsql:equery(Conn, "SELECT * FROM users WHERE id IN ($1::integer[])", [L]), % doesn't work epgsql:equery(Conn, "SELECT * FROM users WHERE id IN unnest($1::integer[])", [L]), % doesn't work
What is the right way to do this?
Advertisement
Answer
With $1 you pass only single value, because the database treats bind variable as some atomic data, not as text placeholder (essentially, the value of bind variable is used after the statement parsing is done). So in your case you pass a list to the database (which, I assume, is converted to PG array).
Postgres documentation says, that for IN it expects a list of scalar expressions, so array is not expanded with this operator. Alternatively, for array comparison it suggests ANY/SOME and ALL expressions (for OR semantic and for AND semantic respectively), where at right hand side you should provide an array to check in.
Since IN is a shorthand for expr = value1 OR expr = value2 OR ..., you need to transform your query to:
epgsql:equery(Conn, "SELECT * FROM users WHERE id = ANY($1::int[])", [L])