Camelcase Column in Postgresql Database in Rails (Activerecord)

camelCase column in postgresql database in rails (ActiveRecord)

In Postgres (as well as in the ISO/ANSI standard of SQL language), object names are case-insensitive.

So objectName is the same as objectname, and you must take it into account when deciding to use camel-cased names.

You can tell Postgres, that you do want to use case-sensitive name – just add double quotes around the name: "objectName". Keep in mind, that later you won't be able to use such object as objectName, it will simply try to find objectname and won't find it, triggerring an error, so using double quotes will be mandatory.

Also, there are some minor caveats when working with double-quoted case-sensitive object names (for instance, psql's \d command will list your object like this: "public.objectName", which is not really correct, the correct name is "public"."objectName", etc).

In few projects, I had camel-style table/column names and it always was some pain, especially when a new developer started to work with such project.

So I'd suggest using underscorded names in SQL always (object_name).

How to force Rails ActiveRecord to use CamelCase in queries, instead of snake_case

I don't think rails have any support as per your specification at base level. As rails believe in convention over configuration.

Although we can override the table name in your model.

user_table.rb

class UserTable < ApplicationRecord

  self.table_name = "UserTable"
  self.primary_key = "YourPrimaryKey"

end

Why am I unable to sort records by a column with a camelCase name

I believe the issue here comes down to your column name, setOrder. If it was lower-cased instead of camel-cased, the issue would go away.

When you created your table, it will look like this in Postgres (excluding a few columns):

database=# \d cardsets
                                  Table "public.cardsets"
  Column  |       Type        | Collation | Nullable |               Default
----------+-------------------+-----------+----------+--------------------------------------
 id       | integer           |           | not null | nextval('cardsets_id_seq'::regclass)
 name     | character varying |           | not null |
 code     | character varying |           | not null |
 setOrder | integer           |           | not null |
Indexes:
    "cardsets_pkey" PRIMARY KEY, btree (id)

When ActiveRecord turns your order string argument into a SQL query, without the double quotes you describe, the column name is folded to lower case. See this answer or this answer for more info. setOrder becomes setorder, which doesn't exist on the table, hence the error.

I think you have two options here: change the column name to set_order which is more conventional PostgreSQL, or setorder, both of which will work from ActiveRecord without double quotes, or continue to use double quotes anytime you need ActiveRecord to query that column. If possible I'd recommend the first approach.

How to pluck a camel case column

That's weird.

Try this. Seems to work fine.

Task.pluck('"practiceType"')

How to Keep Original Column Names with Sequel

You can modify the default mangling on MSSQL by using the identifier_mangling extension:

DB.extension(:identifier_mangling)
DB.identifier_input_method = DB.identifier_output_method = nil

Are PostgreSQL column names case-sensitive?

Identifiers (including column names) that are not double-quoted are folded to lowercase in PostgreSQL. Column names that were created with double-quotes and thereby retained uppercase letters (and/or other syntax violations) have to be double-quoted for the rest of their life:

"first_Name"

Values (string literals / constants) are enclosed in single quotes:

'xyz'

So, yes, PostgreSQL column names are case-sensitive (when double-quoted):

SELECT * FROM persons WHERE "first_Name" = 'xyz';

Read the manual on identifiers here.

My standing advice is to use legal, lower-case names exclusively so double-quoting is not needed.

---

Related Topics