如何使用带有大写列名的Rails？ [英] How to use Rails with uppercase column name?

问题描述

我将以下内容作为AR查询的一部分：

I have the following as part of an AR query:

.having（'COUNT（foo.id）> bar.maxUsers '）

这会产生错误：

ActiveRecord::StatementInvalid:
       PG::UndefinedColumn: ERROR:  column bar.maxusers does not exist
                                                                    ^
       HINT:  Perhaps you meant to reference the column "bar.maxUsers".

我引用列 bar.maxUsers

因此，显然，AR简化了查询。如何抑制这种行为？

So, apparently AR downcases the query. How to suppress this behavior?

Rails 4.2.10

PostgreSQL

Rails 4.2.10
PostgreSQL

编辑： SQL：

SELECT ... HAVING COUNT(foo.id) > bar.maxUsers

所以它是在 to_sql 。也许是通过 execute 执行的？

So it is happening after the to_sql. Maybe from the execute?

推荐答案

这不是ActiveRecord还是AREL问题，这就是区分大小写在SQL和PostgreSQL中的工作方式。

This isn't an ActiveRecord or AREL issue, this is just how case sensitivity works in SQL and PostgreSQL.

SQL中的标识符（例如表名和列名）不区分大小写，除非用引号引起来。标准SQL表示未加引号的标识符折叠成大写，而PostgreSQL则将它们折叠成小写，因此错误消息中的 bar.maxusers 。

Identifiers in SQL (such as table and column names) are case-insensitive unless they're quoted. Standard SQL says that unquoted identifiers are folded to upper case, PostgreSQL folds them to lower case, hence the bar.maxusers in the error message.

解决方案是引用有问题的列名：

The solution is to quote the offending column name:

.having('COUNT(foo.id) > bar."maxUsers"')

请注意，必须使用双引号将标识符引起来因为单引号仅用于字符串文字。还应注意，标识符引用是特定于数据库的：标准SQL和PostgreSQL使用双引号，MySQL使用反引号，SQL Server使用方括号，...

Note that you must use double quotes for quoting the identifier as single quotes are only for string literals. Also note that identifier quoting is database-specific: standard SQL and PostgreSQL use double quotes, MySQL uses backticks, SQL Server uses brackets, ...

这篇关于如何使用带有大写列名的Rails？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！