Postgres: Find matching array or array that contains t

Question

I'm using postgres 9.5 and have a table that looks something like this:

+----+--------------+-------------------------------+
| id | string_field | array_field                   |
|----+--------------+-------------------------------|
| 1  | string a     | [apple, orange, banana, pear] |
| 2  | string b     | [apple, orange, banana]       |
| 3  | string c     | [apple, orange]               |
| 4  | string d     | [apple, pear]                 |
| 5  | string e     | [orange, apple]               |
+----+--------------+-------------------------------+

Is it possible to query the DB for rows where array_field is, or contains [apple, orange, banana]? The results should return rows with id 1 and 2.

souslov · Accepted Answer · 2016-07-14 13:33:22Z

5

Try something like this:

where array_field @> ARRAY['apple', 'orange', 'banana']::varchar[]

Documentation

answered Jul 14, 2016 at 13:33

souslov

44.7k11 gold badges92 silver badges113 bronze badges

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

2 Comments

Zaki Aziz Over a year ago

Thanks for the answer. I've realized I actually have an array in a jsonb column, what would the syntax look like for that?

Zaki Aziz Over a year ago

I've got it: select * from tmp where jsonb_column @> '["apple", "orange", "banana"]'::jsonb;

Collectives™ on Stack Overflow

Postgres: Find matching array or array that contains t

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related