postgresql - Rails reports can't find a column that is there

Question

I am currently trying to do a complicated WHERE search on a table using Rails, the trouble is I get the 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 column actually exists, and doing a 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 definatly in the database, has anyone had any experience with this?

Answer 1

SQL column names are case insensitive unless quoted, the standard says that identifiers should be normalized to upper case but PostgreSQL normalizes to lower case:

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

You're referencing Email in your SQL:

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

but PostgreSQL is complaining about email:

column "email" does not exist

Your unquoted Email is being treated as email because PostgreSQL normalizes identifiers to lower case. Sounds like you created the columns with capitalized names by double quoting them:

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

or by using :Email to identify the column in a migration. If you quote a column name when it is created, then it is not normalized to lower case (or upper case in the SQL standard case) and you'll have to double quote it and match the case forever:

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

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

The best practise is to use lower case column and table names so that you don't have to worry about quoting things all the time. I'd recommend that you fix your table to have lower case column names.

---

As an aside, your 'NULL' string literal:

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

looks odd, are you sure that you don't mean "Username" is null? The 'NULL' string literal and the NULL value are entirely different things and you can't use = or != to compare things against NULL, you have to use is null, is not null, is distinct from, or is not distinct from (depending on your intent) when NULLs might be in play.