Postgres的 - 比较两个数组 [英] postgres - comparing two arrays

问题描述

postgres的具有一个数组数据类型，在这种情况下，一个数字数组：

postgres has an array data type, in this case a numeric array:

CREATE TABLE sal_emp (name text, pay_by_quarter integer[]);
INSERT INTO sal_emp VALUES ('one', '{1,2,3}');
INSERT INTO sal_emp VALUES ('two', '{4,5,6}');
INSERT INTO sal_emp VALUES ('three', '{2,4,6}');
SELECT * FROM sal_emp;

Result:
one, {1,2,3}
two, {4,5,6}
three, {2,4,6}

从我可以告诉，你只能如下查询数组：

From what i can tell, you can only query an array as follows:

SELECT * FROM sal_emp WHERE 4=ANY(pay_by_quarter);
SELECT * FROM sal_emp WHERE ARRAY[4,5,6]=pay_by_quarter;

这意味着你可以选择与阵列的行

包含一个参数匹配，或者如果整个阵列中的参数相匹配。

which means you can select a row with the array contains a match for a single argument, or if the whole array matches an array argument.

我需要选择所在行的阵列中的任何成员的参数数组的任何成员相匹配的行 - 有点像一个'IN'，但我无法弄清楚如何。我已尝试以下两种方法但是没有工作：

I need to select a row where any member of the row's array matches any member of an argument array - kind of like an 'IN' but i can't figure out how. I've tried the following two approaches but neither work:

SELECT * from sal_emp WHERE ARRAY[4,5,6]=ANY(pay_by_quarter);
SELECT * from sal_emp WHERE ANY(pay_by_quarter) IN (4,5,6);

我想我可以做一些与数组转换为字符串，但听起来像可怜的解决方案。

I assume i could do something with converting the array to a string but that sounds like poor solution..

什么想法？

推荐答案

想通它...有一个＆放大器;＆安培;运营商

figured it ... there's an && operator

<一个href=\"http://www.postgresql.org/docs/8.2/static/functions-array.html\">http://www.postgresql.org/docs/8.2/static/functions-array.html

＆放大器;＆放大器;重叠（有共同的元素）ARRAY [1,4,3]放大器;＆安培; ARRAY [2,1]

"&& overlap (have elements in common) ARRAY[1,4,3] && ARRAY[2,1]"

这篇关于Postgres的 - 比较两个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！