How to select by a word in a row from a column which has an array inside?-CodePudding

I have a column that contains an array of strings. I try to select the items which have a certain word in that column but I get this error:

function contains(character varying[], unknown) does not exist

This is my select query:

SELECT "schedule", COUNT("schedule") AS "count" 
FROM "members" AS "member" 
WHERE contains("member"."facility_id", 'DEMO') 
GROUP BY "schedule";

CodePudding user response：

If you want to test if an array of strings contains a specific element you need to use the contains operator which is @> but only works with arrays on both sides

WHERE member.facility_id @> array['DEMO']

To test for a single value, you can also use the any operator:

WHERE 'DEMO' = ANY(member.facility_id)