3

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?

astentx
  • 6,393
  • 2
  • 16
  • 25
AndreyKo
  • 1,421
  • 2
  • 11
  • 25
  • 1
    Bind variables are the data itself, so with `IN( $1 )` you pass only a single value (of type list in your case). So it is not valid from the database point of view. You can try [`execute_batch`](https://hexdocs.pm/epgsql/epgsql.html#execute_batch-3) or try to pass an array and wrap it with `= any` like answered [here](https://stackoverflow.com/questions/11231544/check-if-value-exists-in-postgres-array/11231965). – astentx Apr 14 '21 at 17:51
  • @astentx Hey, thanks, it worked! The correct query was epgsql:equery(Conn, "SELECT * FROM users WHERE id = ANY($1::integer[]), [L]). Can you provide your comment as an answer so I could accept it? – AndreyKo Apr 14 '21 at 18:36

1 Answers1

2

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])
astentx
  • 6,393
  • 2
  • 16
  • 25