Postgresql-specific database error: "ActiveRecord::StatementInvalid: PGError: ERROR: operator does not exist"

Question

My state model looks like this:

class State < ActiveRecord::Base
  belongs_to :country
  named_scope :order_by_name, :order => :name

  validates_presence_of [:country, :name]

  def <=>(other)
    name <=> other.name
  end

end

My country model looks like this:

class Country < ActiveRecord::Base
  has_many :states
  named_scope :order_by_name, :order => :name  

  def <=>(other)
    name <=> other.name
  end
end

This works in a SQLite environment:

>> Country.find_by_iso("US").states
=> [#<State id: 1, name: "Alaska", abbr: "AK",  #  .....

But in a postgresql environment, I get this:

>> Country.find_by_iso("US").states
ActiveRecord::StatementInvalid: PGError: ERROR:  operator does not exist: character varying = integer
LINE 1: SELECT * FROM "states" WHERE ("states".country_id = 214) 
                                                          ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT * FROM "states" WHERE ("states".country_id = 214)

Any thoughts?

Answer 1

The error message is quite clear, you're trying to do a comparison between different types, hence the 'character varying = integer' notice.

The solution: make country_id a column of type 'int' instead of 'varchar'.