Rails 3.1. Heroku PGError: operator does not exist: character varying = integer

Question

Having a little trouble fixing an error.

All works great on local machine. On PG, heroku is the error.

Here are the logs :

  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m ActionView::Template::Error (PGEr
  ror: ERROR:  operator does not exist: character varying = integer
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m LINE 1: ...T "reviews".* FROM "re
  views"  WHERE "reviews"."trip_id" = 32
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m : SELECT "reviews".* FROM "review
  s"  WHERE "reviews"."trip_id" = 32):
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m     31:   <div style='display:non
  e'>
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m     33:      <% for review in @tr
  ip.reviews %>
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m     34:
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m     32:    <div id="inline">
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m HINT:  No operator matches the gi
  ven name and argument type(s). You might need to add explicit type casts.
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m   app/controllers/trips_controlle
  r.rb:21:in `show'
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m cache: [GET /trips/32] miss
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m     36:     <li> <%= review.conte
  nt %> </li>
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m     35:     <ul>
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m   app/views/trips/show.html.erb:3
  3:in `_app_views_trips_show_html_erb__3301405670044045300_69859019468960'
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m Completed 500 Internal Server Err
  or in 86ms
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m   Parameters: {"id"=>"32"}
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m   Processing by TripsController#s
  how as HTML
  ←[32m2012-01-09T19:52:24+00:00 app[web.1]:←[0m Rendered trips/show.html.erb with
  in layouts/application (81.8ms)

Not really sure where exactly, the error occurs and how to fix it.

reviews.rb

 class Review < ActiveRecord::Base
  belongs_to :trip
 end

 class Trip < ActiveRecord::Base
  has_many :reviews, :dependent => :destroy
  attr_accessible, :reviews_attributes

  accepts_nested_attributes_for :reviews, :reject_if => lambda { |a| a[:name].blank? }, :allow_destroy => true
 end 

show.html.rb

 <%= link_to "Read Reviews", '#inline',  :id => 'various1', :class => 'review' %>  

 <div style='display:none'>  
   <div id="inline">
      <% for review in @trip.reviews %>  
       <ul>
         <li> <%= review.content %> </li>
         <li> <i> <%= review.name %> </i> </li>
       </ul>
     <% end %>  
  </div> 
 </div>

The thing that confuses me is that I have two other practically the same models, but they work well.

Thanks!

Solution 1

Your problem is here:

WHERE "reviews"."trip_id" = 32

and the error message says that:

operator does not exist: character varying = integer

so you have created your trip_id column in reviews as a string rather than as an integer. That will work fine in SQLite because SQLite's type system is rather loose but it won't work in PostgreSQL as PostgreSQL is quite a bit stricter.

You could try adding a migration to fix the type of trip_id:

def change
  change_column :reviews, :trip_id, :integer
end

and if that doesn't work then drop and recreate the table:

def change
  drop_table :reviews
  create_table :reviews do |t|
    #...
    t.integer :trip_id
    #...
  end
end

You could also do an ALTER TABLE through raw SQL if you have data that you want to preserve and the change_column doesn't work:

def change
  execute %q{
    alter table reviews
    alter column trip_id
    type int using cast(trip_id as int)
  }
end

That should work in PostgreSQL (but not SQLite) as long as you don't have any broken data in your trip_id.

Once you have that sorted out, you should install PostgreSQL and switch your development environment to that. Developing on top of SQLite and deploying to PostgreSQL (or developing on top of one database and deploying on top of any other database for that matter) is a bad idea and will cause you all sorts of grief and confusion.

Solution 2

You could leave the column as a text/varchar data type, and cast it as an integer...

WHERE "reviews"."trip_id"::int = 32

Solution 3

A simpler way to do the migration is this:

change_column :reviews, :trip_id, 'integer USING CAST(trip_id AS integer)'

Comments

[emilsw]

Thanks! It looks like, I mistyped (integer as string), when creating a model.

[emilsw]

didn't see it locally as sqlite3 is ok with it. You're right, Time to start developing on top of PG.

[Trip]

Hey @mu_is_too_short ! It's been a long time ;) Question, I'm curious if it would still be possible to do this with a string though ( if you really had to ).

[mu is too short]

@Trip I think you'd have to get a type cast in there, either ::int on the left as Stew-au does below or ::text on the right (or use the standard cast(x as integer) or cast(x as text) syntax).