ActionView::Template::Error (PG::UndefinedFunction: ERROR: operator does not exist: integer ~~ unknown

Question

I switched from MySQL to PostgreSQL for use in Heroku. Now my search will not work. Can't figure out what is wrong with the operator. ActionView::Template::Error (PG::UndefinedFunction: ERROR: operator does not exist: integer ~~ unknown.

   2014-11-11T19:59:58.082607+00:00 app[web.1]: Processing by AllListingsController#search_listings as JS
    2014-11-11T19:59:58.105074+00:00 app[web.1]:     4: <% @listings.each do |listing| %>
    2014-11-11T19:59:58.102205+00:00 app[web.1]:                                                           ^
    2014-11-11T19:59:58.105066+00:00 app[web.1]: HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
    2014-11-11T19:59:58.105083+00:00 app[web.1]:   app/controllers/all_listings_controller.rb:14:in `search_listings'
    2014-11-11T19:59:58.110318+00:00 heroku[router]: at=info method=GET path="/search_listings?q=500&_=1415735994648" host=subleasy.herokuapp.com request_id=24a21078-d3fd-4bce-9afb-bfc9d976c0a7 fwd="50.247.32.153" dyno=web.1 connect=1ms service=35ms status=500 bytes=1754
    2014-11-11T19:59:58.103449+00:00 app[web.1]: Completed 500 Internal Server Error in 21ms
    2014-11-11T19:59:58.102207+00:00 app[web.1]: HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
    2014-11-11T19:59:58.105076+00:00 app[web.1]:     5:   $("div.search_list").append("<%= escape_javascript(render('all_listings/listings_partial', a_listing: listing )) %>")
    2014-11-11T19:59:58.105073+00:00 app[web.1]:     3:
    2014-11-11T19:59:58.103270+00:00 app[web.1]:   Rendered all_listings/search_listings.js.erb (5.1ms)
    2014-11-11T19:59:58.105071+00:00 app[web.1]:     2: $("div.search_list").html("")
    2014-11-11T19:59:58.105081+00:00 app[web.1]:   app/views/all_listings/search_listings.js.erb:4:in `_app_views_all_listings_search_listings_js_erb___729824412217144086_70218143810320'
    2014-11-11T19:59:58.102209+00:00 app[web.1]: : SELECT  "listings".* FROM "listings"  WHERE (rent LIKE '%500%' OR city LIKE '%500%' OR state LIKE '%500%' OR address LIKE '%500%')  ORDER BY rent asc LIMIT 5 OFFSET 0
    2014-11-11T19:59:58.105078+00:00 app[web.1]:     6: <% end %>
    2014-11-11T19:59:58.102199+00:00 app[web.1]: PG::UndefinedFunction: ERROR:  operator does not exist: integer ~~ unknown
    2014-11-11T19:59:58.105068+00:00 app[web.1]: : SELECT  "listings".* FROM "listings"  WHERE (rent LIKE '%500%' OR city LIKE '%500%' OR state LIKE '%500%' OR address LIKE '%500%')  ORDER BY rent asc LIMIT 5 OFFSET 0):
    2014-11-11T19:59:58.105084+00:00 app[web.1]:
    2014-11-11T19:59:58.082630+00:00 app[web.1]:   Parameters: {"q"=>"500", "_"=>"1415735994648"}
    2014-11-11T19:59:58.105058+00:00 app[web.1]:
    2014-11-11T19:59:58.105061+00:00 app[web.1]: ActionView::Template::Error (PG::UndefinedFunction: ERROR:  operator does not exist: integer ~~ unknown
    2014-11-11T19:59:58.105063+00:00 app[web.1]: LINE 1: SELECT  "listings".* FROM "listings"  WHERE (rent LIKE '%500...
    2014-11-11T19:59:58.105065+00:00 app[web.1]:                                                           ^
    2014-11-11T19:59:58.077155+00:00 app[web.1]: Started GET "/search_listings?q=500&_=1415735994648" for 50.247.32.153 at 2014-11-11 19:59:58 +0000
    2014-11-11T19:59:58.102203+00:00 app[web.1]: LINE 1: SELECT  "listings".* FROM "listings"  WHERE (rent LIKE '%500...
    2014-11-11T19:59:58.105070+00:00 app[web.1]:     1:
    2014-11-11T19:59:58.105085+00:00 app[web.1]:
    2014-11-11T19:59:58.105079+00:00 app[web.1]:     7: $(".hidetable").hide()

My method

def search_listings
    @listings = Listing.where("rent LIKE ? OR city LIKE ? OR state LIKE ? OR address LIKE ?", "%#{params[:q]}%", "%#{params[:q]}%", "%#{params[:q]}%", "%#{params[:q]}%").order(sort_column + " " + sort_direction).paginate(:per_page => 5, :page => params[:page])
    respond_to do |format|
      format.js
    end
end
def sort_column
      Listing.column_names.include?(params[:sort]) ? params[:sort] : "rent"
    end 
def sort_direction
    %w[asc desc].include?(params[:direction]) ? params[:direction] : "asc"
end

schema.rb

create_table "listings", force: true do |t|
    t.integer  "user_id"
    t.string   "address"
    t.string   "city"
    t.string   "state"
    t.date     "lease_start"
    t.string   "lease_length"
    t.string   "lease_type"
    t.integer  "rooms"
    t.text     "description"
    t.string   "email"
    t.string   "phone"
    t.integer  "rent"
    t.integer  "zipcode"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.string   "photo_file_name"
    t.string   "photo_content_type"
    t.integer  "photo_file_size"
    t.datetime "photo_updated_at"
    t.float    "latitude",           limit: 24
    t.float    "longitude",          limit: 24
  end

why won't this work in postgreSQL when it works flawlessly in Mysql???

Answer 1

It seems that you are trying to use a LIKE operator on an integer column (namely rent). I don't believe this will work (at least in postgres).

According to the answers here you might want to try to add a cast:

@listings = Listing.where("cast(rent as text) LIKE ? OR city LIKE ? OR ...