Postgresql 选择列 = 数组的行 [英] Postgresql Select rows where column = array

问题描述

这是我正在尝试做的事情的总结:

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;

那么 PostgreSQL 是否支持使用数组进行搜索，或者我是否必须执行类似于我的解决方案的操作?

So is there support in PostgreSQL to use an array to search, or do I have to do something similar to my solution?

推荐答案

SELECT  *
FROM    table
WHERE   some_id = ANY(ARRAY[1, 2])

或 ANSI 兼容:

SELECT  *
FROM    table
WHERE   some_id IN (1, 2)

首选 ANY 语法，因为可以将数组作为一个整体传递给绑定变量:

The ANY syntax is preferred because the array as a whole can be passed in a bound variable:

SELECT  *
FROM    table
WHERE   some_id = ANY(?::INT[])

您需要传递数组的字符串表示形式:{1,2}

You would need to pass a string representation of the array: {1,2}

这篇关于Postgresql 选择列 = 数组的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！