Rails 报告找不到存在的列 [英] Rails reports can't find a column that is there
问题描述
我目前正在尝试使用 Rails 对表进行复杂的 WHERE 搜索，但问题是出现错误:
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' ))
而且我知道该列确实存在，并且执行 rails 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)
所以这肯定在数据库中，有人有这方面的经验吗?
So this is definatly in the database, has anyone had any experience with this?
推荐答案
SQL 列名不区分大小写，除非引用，标准说标识符应该规范化为大写，但 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、foo 和 "foo" 被 PostgreSQL 认为是相同的，但 "Foo"code> 和 "FOO" 与这三个不同，并且彼此不同.(PostgreSQL 中将未加引号的名称折叠为小写与 SQL 标准不兼容，该标准规定未加引号的名称应折叠为大写.因此，foo 应等效于 "FOO" 不是 "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,fooshould 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 中引用了 Email:
You're referencing Email in your SQL:
SELECT "bans".* FROM "bans" WHERE (Email='' ...
但是 PostgreSQL 正在抱怨 email:
but PostgreSQL is complaining about email:
column "email" does not exist
您未加引号的 Email 被视为 email，因为 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(...)
...
)
或通过使用 :Email 来标识迁移中的列.如果您在创建列名时引用它，那么它不会被规范化为小写(或 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"='' ...
一旦您修复了Email，IP、Username、Reason 都会遇到同样的问题，和 Length:您必须在引用它们的任何 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 进行比较，你必须使用is null，is not 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.
这篇关于Rails 报告找不到存在的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
