Column does not exist error in PostgreSQL database and Rails 3 app

Question

I am getting the error below when trying to query my PostgreSQL database. I can view the table and all columns in pgAdmin and even perform a select *, so I know the table and column exists. Any help with this will be greatly appreciated.

Here is the error I am getting:

PG::Error: ERROR:  column "fi_ase" does not exist

Here is the schema for the table in question. It was generated with a migration as part of a Rails 3.2 app.

create_table "certificates", :force => true do |t|
  t.integer  "profile_id"
  t.boolean  "FI_ASE"
  t.boolean  "FI_AME"
  t.boolean  "FI_INSTA"
  t.datetime "created_at",    :null => false
  t.datetime "updated_at",    :null => false
  t.boolean  "C_ASEL"
  t.boolean  "C_AMEL"
  t.boolean  "C_ASES"
  t.boolean  "C_AMES"
  t.boolean  "ATP_ASEL"
  t.boolean  "ATP_AMEL"
  t.boolean  "ATP_ASES"
  t.boolean  "ATP_AMES"
  t.boolean  "GI_Basic"
  t.boolean  "GI_Advanced"
  t.boolean  "GI_Instrument"
end

Here is my query/method in Rails:

def self.search(city, state, zip, *certs)
  query_obj = joins(:profile => [:addresses, :certificate])
  query_obj = query_obj.where("city like ?", "%#{city}%") unless city.blank?
  query_obj = query_obj.where("state = ?", state) unless state.blank?
  query_obj = query_obj.where("zip like ?", "%#{zip}%") unless zip.blank?
  query_obj = query_obj.where("FI_ASE = ?", true) unless certs[0].blank?

  query_obj
end

I get the same error when running the following SQL statement directly in my pgAmin SQL Editor:

select *
from contacts c
inner join profiles p on c.id = p.contact_id
inner join addresses a on p.id = a.profile_id
inner join certificates ct on p.id = ct.profile_id
where ct.FI_ASE = true

Can you describe the database table certificates. "\d certificates" — manoj
– manoj, Commented Jan 30, 2013 at 5:53

mu is too short · Accepted Answer · 2013-01-30 06:16:12Z

Rails will double quote the column names when it generates them. For example, the CREATE TABLE for your table will look like this when PostgreSQL sees it:

create table "certificates" (
  -- ...
  "FI_ASE" boolean,

When an identifier is double quoted, it is case sensitive. But, PostgreSQL will normalize unquoted identifiers to lower case so when you say this:

query_obj.where("FI_ASE = ?", true)

the SQL will come out as:

where FI_ASE = 't'

but, since your FI_ASE is not quoted, PostgreSQL will see that as:

where fi_ase = 't'

However, your table doesn't have an fi_ase column, it has an FI_ASE column.

Now that we know what's wrong, how do we fix it? You can manually quote the column name all the time:

where('"FI_ASE" = ?', true)

or you could let ActiveRecord do it (but make sure you use the right case):

where(:FI_ASE => true)

or best of all, recreate the table using lower case column names so that you don't have to quote things.

Collectives™ on Stack Overflow

Column does not exist error in PostgreSQL database and Rails 3 app

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related