CODEWITHSUNDEEP
postgresql
Christian-G
Christian-G

Reputation: 2361

Query array field for multiple values

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.

Upvotes: 2

Views: 5885

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324511

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.

Upvotes: 9

Related Questions