Query array field for multiple values

Question

I have a `roles` field in a `Users` table of type `Array`. I need to be able to get all users that have both role 'admin' and role 'member'. FOr now I use this query: `select * from users where 'member' = ANY(roles) and 'admin' = ANY(roles);` I was wondering if there was a cleaner way to do this.

Accepted Answer

Use the array-contained-by operator `@<`:

```
select * from users where ARRAY['member','admin']::varchar[] <@ roles;
```

That'll let you index the lookup too.

(Correction per @bereal; I misread the question)

Or, if you meant that they must have both rights, use array-overlaps `&&`:

```
select * from users where ARRAY['member','admin']::varchar[] && roles;
```

Also, as your input turns out to be `varchar[]` (you didn't show your table definition), you must cast the array input to `varchar[]` too, as there's no implicit cast between array types.

Query array field for multiple values

Solution

Problem