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.

Craig Ringer · Accepted Answer · 2014-07-16 12:06:36Z

9

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.

edited Jul 16, 2014 at 12:06

answered Jul 16, 2014 at 10:42

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

4 Comments

bereal Over a year ago

As far as I understand, that will be equal to 'member' = ANY(roles) OR 'admin' = ANY(roles), so rather <@ aka "is contained by".

Christian-G Over a year ago

Using that method, I get: ERROR: operator does not exist: text[] && character varying[]

Craig Ringer Over a year ago

@yossarian There's no implicit cast from text[] to varchar[] so you have to cast explicitly; see edit.

Clodoaldo Neto Over a year ago

@yossarian You should rewrite your question to I need to be able to get all users that have both role 'admin' and role 'member'

Collectives™ on Stack Overflow

Query array field for multiple values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related