Rails reports cannot find a column that is

Question

I am currently trying to do a complex WHERE search in a table using Rails, the problem is that I am getting an error:

 PG::Error: ERROR: column "email" does not exist LINE 1: SELECT "bans".* FROM "bans" WHERE (Email='' AND IP='' AND (... ^ : SELECT "bans".* FROM "bans" WHERE (Email='' AND IP='' AND (Username='NULL' )) 

And I know that the column really exists, and running rails dbconsole gives me the following:

 Jungle=> select * from bans; id | Username | IP | Email | Reason | Length | created_at | updated_at ----+----------+----+-------+--------+--------+------------+------------ (0 rows) 

So this is definitely in the database, does anyone have any experience with this?

Answer 1

SQL column names are case insensitive, if not specified, the standard says that identifiers should be normalized in uppercase, but PostgreSQL is normalized to lowercase:

Quoting an identifier also makes it case sensitive, while unnamed names always appear in lower case. For example, the identifiers FOO , FOO and "foo" are considered identical by PostgreSQL, but "foo" and "foo" are different from these three and to each other. (PostgreSQL's lowercase undefined names are not compatible with the SQL standard, which states that unquoted names must be uppercase. Thus, FOO must be equivalent to "foo" not "foo" according to the standard. If you want to write portable applications, you are advised to always indicate a specific name or never quote it.)

You are referring to Email in your SQL:

 SELECT "bans".* FROM "bans" WHERE (Email='' ... 

but PostgreSQL complains about Email :

 column "email" does not exist 

Your unspecified Email treated as Email because PostgreSQL normalizes identifiers in lower case. It looks like you created the columns with capital names by specifying them twice:

 create table "bans" ( "Email" varchar(...) ... ) 

or using :Email to identify the column during migration. If you specify a column name when creating it, then it will not be normalized in lower case (or in upper case in the standard SQL case), and you will have to specify it twice and match the substance forever:

 SELECT "bans".* FROM "bans" WHERE ("Email"='' ... 

Once you fix Email , you will have the same problem with IP , Username , Reason and Length : you will have to double-quote them all in any SQL that references them.

Best practice is to use lowercase column and table names so you don't have to worry about quoting things all the time. I would recommend that you correct the table to have lowercase column names.

---

Aside, your 'NULL' string literal:

 SELECT "bans".* FROM "bans" WHERE (Email='' AND IP='' AND (Username='NULL' )) -- -------------------->------------------>---------->---------------^^^^^^ 

It looks weird, are you sure you don't mean "Username" is null ? The string literal 'NULL' and the NULL value are completely different things, and you cannot use = or != To compare things with NULL, you must use is null , is not null , is distinct from or is not distinct from (depending from your intention) when they can play NULL.

Answer 2

It doesn't seem like you are mistaken from the test database, but if so, try rake db:test:prepare .

In general, keep in mind that you have 3 databases - Test, Development, Production. Therefore, it is easy to confuse and check the wrong one.

Answer 3

I had the same problem

but as mu-is-too-short said, PostgreSql can be said to be case sensitive by column names.

Thus, by implementing this code, I managed to get around the same error that you encountered:

 Transaction.find(:all,:conditions => ["(date between ? and ?) AND \"Membership_id\" = ?", Time.now.at_beginning_of_month, Time.now.end_of_month,membership.id]) 

Have you noticed the \ " sign hanging the column name? Well .. how would it be annoying that the fix for this problem.