导轨报告找不到列，它是有 [英] Rails reports can't find a column that is there

问题描述

我目前正在做一个复杂的，其中搜索上使用Rails表，麻烦是我得到的错误：

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' ))

和我知道，列确实存在，并做了轨道DBCONSOLE 给我以下内容：

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)

这就是definatly在数据库中，有没有人有这方面的经验？

So this is definatly in the database, has anyone had any experience with this?

推荐答案

SQL列名不区分大小写，除非引用的标准说标识符应该归为大写字母，但<一个href="http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS">PostgreSQL标准化为小写：

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:

把一个标识符也使得它大小写敏感，而没有引用的名字总是转成小写。例如，标识符 FOO ，富和富被认为是一样的PostgreSQL，但富和FOO是这三个，每个不同的其他。 （不带引号的名称于PostgreSQL小写的折叠与SQL标准，它说，不带引号的名称应变成大写不兼容。因此，富应相当于FOO不是富根据标准。如果你想写建议您始终便携式应用举一个特定的名称或就坚决不引。）

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.)

您正在引用电子邮件在SQL：

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

但PostgreSQL的抱怨电子邮件：

column "email" does not exist

您不带引号电子邮件被视为电子邮件，因为PostgreSQL的标准化标识符为小写。听起来像是你用双引号他们创建了大写的名字列：

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(...)
    ...
)

或使用：电子邮件来确定迁移列。如果报价被创建时列名，那么它是不是规范化为小写（或SQL标准的情况下，大写），你就会有双引号，并匹配的情况永远的：

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"='' ...

在你修复电子邮件，你就会有同样的问题与 IP ，用户名，原因和长度：你必须双引号他们都在任何SQL引用它们。

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.

顺便说一句，你的NULL字符串：

As an aside, your 'NULL' string literal:

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

看起来很奇怪，你确定你不是说用户名为空？该NULL字符串和空值是完全不同的事情，你不能使用 = 或！= 对NULL进行比较的东西，你必须使用的 为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.

这篇关于导轨报告找不到列，它是有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！