How to Reference CamelCase Column Names in a Postgres Database

Assuming you have a Postgres database table like the following...

$ psql -d my_postgres_db_name

psql (9.2.1)
Type "help" for help.

my_postgres_db_name=# \d+ products
                                                                 Table "public.products"
          Column           |            Type             |                          Modifiers                           | Storage  | Stats target | Description
---------------------------+-----------------------------+--------------------------------------------------------------+----------+--------------+-------------
 id                        | integer                     | not null default nextval('products_id_seq'::regclass)        | plain    |              |
 title                     | character varying(255)      |                                                              | extended |              |
 primarySupplierId         | integer      

If you try to reference the primarySupplierId through ActiveRecord like following you'll this ERROR:

my_postgres_db_name=# select primarySupplierId from products limit 1;
 ERROR:  column "primarySupplierId" does not exist
 LINE 1: select primarySupplierId from products limit 1;
                ^

ActiveRecord

The same is true if you try to access it via ActiveRecord:

>> Product.select('id, title, primarySupplierId').limit(2)
  Product Load (0.4ms)  SELECT id, title, primarySupplierId FROM "products" LIMIT 2
Hirb Error: PG::Error: ERROR:  column "primarySupplierId" does not exist
LINE 1: SELECT  id, title, primarySupplierId FROM "product...
                           ^
: SELECT  id, title, primarySupplierId FROM "products"  LIMIT 2
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:1153:in `async_exec'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:1153:in `exec_no_cache'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:662:in `block in exec_query'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.11/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:661:in `exec_query'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:1248:in `select'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/querying.rb:38:in `block in find_by_sql'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/explain.rb:40:in `logging_query_plan'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/querying.rb:37:in `find_by_sql'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/relation.rb:171:in `exec_queries'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/relation.rb:160:in `block in to_a'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/explain.rb:33:in `logging_query_plan'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/relation.rb:159:in `to_a'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/activerecord-3.2.11/lib/active_record/relation/delegation.rb:6:in `to_ary'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/hirb-0.7.1/lib/hirb/formatter.rb:88:in `Array'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/hirb-0.7.1/lib/hirb/formatter.rb:88:in `determine_output_class'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/hirb-0.7.1/lib/hirb/formatter.rb:53:in `format_output'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/hirb-0.7.1/lib/hirb/view.rb:204:in `render_output'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/hirb-0.7.1/lib/hirb/view.rb:123:in `view_output'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/hirb-0.7.1/lib/hirb/view.rb:200:in `view_or_page_output'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/hirb-0.7.1/lib/hirb/view.rb:186:in `output_value'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb.rb:160:in `block (2 levels) in eval_input'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb.rb:273:in `signal_status'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb.rb:156:in `block in eval_input'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb/ruby-lex.rb:243:in `block (2 levels) in each_top_level_statement'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb/ruby-lex.rb:229:in `loop'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb/ruby-lex.rb:229:in `block in each_top_level_statement'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb/ruby-lex.rb:228:in `catch'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb/ruby-lex.rb:228:in `each_top_level_statement'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb.rb:155:in `eval_input'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb.rb:70:in `block in start'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb.rb:69:in `catch'
    /Volumes/my_volume_name/Users/lex/.rbenv/versions/1.9.3-p194/lib/ruby/1.9.1/irb.rb:69:in `start'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/railties-3.2.11/lib/rails/commands/console.rb:47:in `start'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/railties-3.2.11/lib/rails/commands/console.rb:8:in `start'
    /myapps/myapp/vendor/bundle/ruby/1.9.1/gems/railties-3.2.11/lib/rails/commands.rb:41:in `<top (required)>'
    script/rails:6:in 'require'
    script/rails:6:in '<main>'

Solution

Just double quote the CamelCased column name, i.e., "primarySupplierId"

my_postgres_db_name=# select "primarySupplierId" from products limit 1;
primarySupplierId
----------------------
384
(1 row)

ActiveRecord

It works for ActiveRecord, too.

>> Product.select('id, title, "primarySupplierId"').limit(2)
  Product Load (0.8ms)  SELECT id, title, "primarySupplierId" FROM "products" LIMIT 2
+------+--------------------------------------------------+----------------------+
| id   | title                                            | primarySupplierId    |
+------+--------------------------------------------------+----------------------+
| 1791 | Hot Air Baloons                                  | 384                  |
| 1792 | Red Wines                                        | 384                  |
+------+--------------------------------------------------+----------------------+
2 rows in set

Sponsor Ads(Please visit one if you liked this article. Thanks!)