ActionView::Template::Error（PG :: UndefinedFunction：ERROR：operator does not exist：integer ~~ unknown）

Question

我从MySQL切换到了用于Heroku的PostgreSQL。现在我的搜索功能无法正常工作，但我无法弄清楚操作符出了什么问题。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()

我的方法

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

为什么这在MySQL中完美运行，而在PostgreSQL中却无法工作？

- robbo

---

顺便提一下，t.integer "zipcode" 是错误的，邮政编码不是一个数字，它是一个由数字字符组成的字符串。 - mu is too short

Answer 1

看起来你想在一个整数列（即租金）上使用一个LIKE运算符，我不认为这会起作用（至少在Postgres中不会）。

根据这里的答案，你可能想尝试添加一个转换：

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

- Uri Agassi