CODEWITHSUNDEEP
ruby-on-railspostgresql
luke
luke

Reputation: 1578

Operator does not exist: character varying[]

Running a simple statement on my User model but keep on getting errors about the operator not matching any of the given arguments. I've never run into anything like this before and don't know how to handle it. I understand I have to perhaps add type casts, but in my POSTGRES database the column keywords is of type array.

@users = User.where("keywords ILIKE ?", "%#{params[:keywords]}%")

How do you handle this?

Exact error message:

ActionView::Template::Error (PG::SyntaxError: ERROR:  syntax error at or near "ANY"
LINE 1: SELECT "users".* FROM "users" WHERE (ANY(keywords) LIKE '%an...
                                         ^
: SELECT "users".* FROM "users" WHERE (ANY(keywords) LIKE '%angularjs%')):

Thanks!

Upvotes: 0

Views: 1928

Answers (1)

Eyeslandic
Eyeslandic

Reputation: 14900

If you want to search in a postgres array you use the any operator. Note, this solution assumes params[:keywords] is just a single string like apples or something. It also matches on exact match, not substrings.

@users = User.where('? = ANY(keywords)', params[:keywords])

If you need to use the ILIKE operator you must to something like

@users = User.where("array_to_string(names, ',') ILIKE ?", "%#{params[:keywords]}%")

or if you want to be fancy with regex

@users = User.where("array_to_string(names, ',') ~* ?", params[:keywords])

Upvotes: 2

Related Questions