Postgresql 选择列 = 数组的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10738446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql Select rows where column = array
提问by Jimmy Pitts
This is a summary of what I am trying to do:
这是我正在尝试做的事情的总结：
$array[0] = 1;
$array[1] = 2;
$sql = "SELECT * FROM table WHERE some_id = $array"
Obviously, there are some syntax issues, but this is what I want to do, and I haven't found anything yet that shows how to do it.
显然，存在一些语法问题，但这是我想要做的，而且我还没有找到任何显示如何去做的东西。
Currently, my plan is to do something along these lines:
目前，我的计划是按照以下方式做一些事情：
foreach($idList as $is)
$where .= 'some_id=' . $id . ' OR';
endforeach
$sql = "SELECT * FROM table WHERE " . $where;
So is there support in PostgreSQL to use an array to search, or do I have to do something similar to my solution?
那么 PostgreSQL 是否支持使用数组进行搜索，或者我是否必须执行类似于我的解决方案的操作？
回答by Quassnoi
SELECT *
FROM table
WHERE some_id = ANY(ARRAY[1, 2])
or ANSI-compatible:
或 -ANSI兼容：
SELECT *
FROM table
WHERE some_id IN (1, 2)
The ANYsyntax is preferred because the array as a whole can be passed in a bound variable:
该ANY语法是首选的，因为可以在绑定变量中传递整个数组：
SELECT *
FROM table
WHERE some_id = ANY(?::INT[])
You would need to pass a string representation of the array: {1,2}
您需要传递数组的字符串表示形式： {1,2}
回答by Ufos
For dynamic SQL use:
对于动态 SQL 使用：
'IN(' ||array_to_string(some_array, ',')||')'
'IN(' ||array_to_string(some_array, ',')||')'
Example
例子
DO LANGUAGE PLPGSQL $$
DECLARE
some_array bigint[];
sql_statement text;
BEGIN
SELECT array[1, 2] INTO some_array;
RAISE NOTICE '%', some_array;
sql_statement := 'SELECT * FROM my_table WHERE my_column IN(' ||array_to_string(some_array, ',')||')';
RAISE NOTICE '%', sql_statement;
END;
$$;
Result:
NOTICE: {1,2}
NOTICE: SELECT * FROM my_table WHERE my_column IN(1,2)
结果：
NOTICE: {1,2}
NOTICE: SELECT * FROM my_table WHERE my_column IN(1,2)
回答by Alejandro Salamanca Mazuelo
$array[0] = 1;
$array[2] = 2;
$arrayTxt = implode( ',', $array);
$sql = "SELECT * FROM table WHERE some_id in ($arrayTxt)"
