我找不到在sql where in子句中为epgsql：equery函数提供值列表的正确语法。我在谷歌上搜索并尝试了几种变体，所有的都失败了。下面是我的一些尝试：
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正确的做法是什么？
1条答案
按热度按时间ep6jt1vc1#
With
$1you 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
INit expects a list of scalar expressions, so array is not expanded with this operator. Alternatively, for array comparison it suggestsANY/SOMEandALLexpressions (forORsemantic and forANDsemantic respectively), where at right hand side you should provide an array to check in.Since
INis a shorthand forexpr = value1 OR expr = value2 OR ..., you need to transform your query to: