时间戳

首页 > 解决方案 > select all rows where array elements exist

postgresql - select all rows where array elements exist

问题描述

I have a table:

CREATE TABLE test_array
(
  id integer,  
  arr TEXT[]
);

I insert sample data:

INSERT INTO test_array(id, arr) VALUES (1, '{"a", "b", "c"}');
INSERT INTO test_array(id, arr) VALUES (2, '{"d", "f", "c"}');
INSERT INTO test_array(id, arr) VALUES (3, '{"a", "z", "i"}');

I want to get rows where elements {"a", "c"} is exist, so the result must be:

'{"a", "b", "c"}'
'{"d", "f", "c"}'
'{"a", "z", "i"}'

I write query:

select * from test_array where arr @> '{"a"}' or arr @> '{"c"}';

but I want to make query without or, in one condition. Is it possible?

When I run select * from test_array where arr @> '{"a", "c"}';

it returns me only one row

https://rextester.com/ATMU4521

标签： postgresql

解决方案

表示“包含”，因此右侧数组中的@>所有元素都必须存在于左侧数组中。您正在寻找被描述为“具有共同元素”的重叠&&运算符：

select * 
from test_array 
where arr && array['a', 'c'];

我更喜欢array[]指定数组常量的表示法，因为我不需要考虑嵌套引号。

推荐阅读