0

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";
FaFa
  • 358
  • 2
  • 16
  • 2
    Well, there is no function `contains()` in Postgres. What mad you think there is? –  Oct 07 '22 at 12:34
  • @a_horse_with_no_name this one: https://stackoverflow.com/questions/7510646/like-vs-contains-on-sql-server, I have tried this one too but I got another error: https://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words , so I searched on tried the other answer – FaFa Oct 07 '22 at 12:37
  • 2
    Neither of those questions are for Postgres. –  Oct 07 '22 at 12:38
  • @a_horse_with_no_name I thought they would work since both use SQL. :( – FaFa Oct 07 '22 at 12:39
  • 1
    Both refer to a full text search in SQL Server, not for looking up elements in an array. –  Oct 07 '22 at 12:43

1 Answers1

1

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)