How do you do a "where in" sql query with an array data type?

Question

I have a field:

dtype ==> character varying(3)[]

... but it's an array. So I have for example:

ID | name | dtype
1  | one  | {'D10', 'D20', 'D30'}
2  | sam  | {'D20'}
3  | jax  | {'D10', 'D20'}
4  | pam  | {'D10', 'D30'}
5  | pot  | {'D10'}

I want to be able to do something like this:

select * from table where dtype in ('D20', 'D30')

This syntax doesnt work, but the goal is to then return fields 1,2,3,4 but not 5.

Is this possible?

I would say by using 'overlap' operator &&... postgresql.org/docs/9.1/static/functions-array.html — Pavel Horal
– Pavel Horal, Commented May 23, 2014 at 9:16
Your table definition and the contents don't match. character varying(3) is not an array. It's a varchar column with a max. length of 3. But if that were true, you wouldn't be able to store something like {'D10', 'D20', 'D30'}. So I guess your column is actually character varying[]? Can you do a \d table_name and show us the complete table definition? — user330315
– user330315, Commented May 23, 2014 at 9:26
if dtype is just a normal varchar, postgresql won't know you have placed some data in it that looks like some form of array. Can you use real postgresql arrays ? — nos
– nos, Commented May 23, 2014 at 9:26
So far "unnest" seems to be the best way to go about it, but I was hoping there'd be a better solution. — rockstardev
– rockstardev, Commented May 23, 2014 at 9:29
you can use the contains operator as Pavel has suggested: dtype && array['D20', 'D30']::varchar[] — user330315
– user330315, Commented May 23, 2014 at 9:53

Erwin Brandstetter · Accepted Answer · 2014-05-24 00:33:32Z

1

select * from table where dtype && ARRAY['D20', 'D30']

668k159 gold badges1.2k silver badges1.3k bronze badges

answered May 23, 2014 at 11:01

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

1 Answer 1