How to use Rails with uppercase column name?

Question

I have the following as part of an AR query:

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

This generates an error:

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

I am referencing the column bar.maxUsers.

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

Rails 4.2.10
PostgreSQL

EDIT: SQL:

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

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

Leifeste

Can you try to add to_sql at the end of the query and post the rendered query please ? – Thorvald

Interesting... Haven't seen this before, but I might try using Arel, to see if it mangles it, too... (I'm assuming you can't easily adjust the column name to something more Railsy) – Selenography

@Thorvald - Please see update. – Leifeste

Maybe you did not use the table name in plural form? – Subservience

Answer 1

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

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

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

Drummer