select all rows where array elements exist

Question

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

user330315 · Accepted Answer · 2019-11-22 16:06:09Z

3

The @> means "contains" so all elements from the array on the right hand side must exist in the array on the left hand side. You are looking for the overlaps && operator which is described as "have elements in common":

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

I prefer the array[] notation to specify array constant as I don't need to think about nested quotes.

answered Nov 22, 2019 at 16:06

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select all rows where array elements exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related