How to filter this field of type array in PostgreSQL?
I have a X table with a Y field of type character varying[] with two records:
{'meeting','lunch','training','presentation'}
{'breakfast','consulting', 'meeting'}
In this context how to make a query of type:
SELECT * FROM X WHERE Y CONTAINS('lunch')
contains() will not work even, you need to use a more appropriate function, any() , as the cantoni said or use the operator that searches the columns of type array :
SELECT * FROM X WHERE Y @> ARRAY['lunch']::varchar[]
Obviously the data needs to be in the proper format.
One option is to use ANY or SOME (actually, SOME is an alias for ANY).
SELECT * FROM X WHERE 'lunch' = ANY (Y)