CODEWITHSUNDEEP
sqlpostgresqlcharvarying
user181452
user181452

Reputation: 585

Query Char varying postgres

I've been trying to make the following query works, but I couldn't:

   SELECT "users".* FROM "users" WHERE (users.roles LIKE "%sales%")
or
   SELECT "users".* FROM "users" WHERE (users.roles LIKE '%sales%')

where roles, can contain: 

 {operations,business_development,sales,customer_service,manager}

and roles is: character varying[]

I'm getting the error:

ERROR: operator does not exist: character varying[] ~~ unknown LINE 1: select users.id from users where users.roles LIKE '%sales%'...

Upvotes: 3

Views: 7215

Answers (1)

e4c5
e4c5

Reputation: 53774

Try

SELECT "users".* FROM "users" WHERE  'sales' = ANY(users.roles)

Having said that, it's worth noting:

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

http://www.postgresql.org/docs/9.5/static/arrays.html

Upvotes: 8

Related Questions