Mysql query not working in postgresql in rails 3.2.11

Question

I am using the following query in rails

grouped_link_counters = uattachment).link_counters.group("year(created_at), month(created_at)").count("created_at")

And it gives following result in rails console using mysql adapter.

  UserAttachment Load (0.2ms)  SELECT `user_attachments`.* FROM `user_attachments` WHERE `user_attachments`.`id` = 132 LIMIT 1
   (0.2ms)  SELECT COUNT(`link_counters`.`created_at`) AS count_created_at, year(created_at), month(created_at) AS year_created_at_month_created_at FROM `link_counters` WHERE `link_counters`.`user_attachment_id` = 132 GROUP BY year(created_at), month(created_at)
 => {11=>9, 12=>15, 1=>1, 2=>1}

But when I use postgresql the same command throws following error :-

SELECT COUNT("link_counters"."created_at") AS count_created_at, year(created_at), month(created_at) AS year_created_at_month_created_at FROM "link_counters" WHERE "link_counters"."user_attachment_id" = 1 GROUP BY year(created_at), month(created_at)
ActiveRecord::StatementInvalid: PG::Error: ERROR:  function year(timestamp without time zone) does not exist
LINE 1: ...link_counters"."created_at") AS count_created_at, year(creat...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
: SELECT COUNT("link_counters"."created_at") AS count_created_at, year(created_at), month(created_at) AS year_created_at_month_created_at FROM "link_counters"  WHERE "link_counters"."user_attachment_id" = 1 GROUP BY year(created_at), month(created_at)
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:1153:in `async_exec'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:1153:in `exec_no_cache'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:662:in `block in exec_query'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activesupport-3.2.11/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:661:in `exec_query'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/connection_adapters/postgresql_adapter.rb:1248:in `select'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/relation/calculations.rb:289:in `execute_grouped_calculation'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/relation/calculations.rb:206:in `perform_calculation'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/relation/calculations.rb:159:in `calculate'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/activerecord-3.2.11/lib/active_record/relation/calculations.rb:58:in `count'
    from (irb):63
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/railties-3.2.11/lib/rails/commands/console.rb:47:in `start'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/railties-3.2.11/lib/rails/commands/console.rb:8:in `start'
    from /home/nishutosh/.rvm/gems/ruby-1.9.3-p194@cdrop/gems/railties-3.2.11/lib/rails/commands.rb:41:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'1.9.3p194 :064 >

Can anybody tell me what can be done to get the same response from postgresql. Thanks in advance!

Read the message: "ERROR: function year(timestamp without time zone) does not exist". This function doesn't exist in PostgreSQL, but you could create it yourself. — Frank Heikens
– Frank Heikens, Commented Feb 12, 2013 at 13:25
See here: postgresql.org/docs/current/static/functions-datetime.html — user330315
– user330315, Commented Feb 12, 2013 at 13:28
Use extract. More here: stackoverflow.com/questions/4493705/… — RAJ
– RAJ, Commented Feb 12, 2013 at 17:34

RAJ · Accepted Answer · 2013-02-13 09:48:33Z

1

In PostgreSQL, we don't have helper functions like YEAR, MONTH etc. Instead of these, you can use date_part in PostgreSQL for same result as MySQL.

E.g.

grouped_link_counters = uattachment.link_counters.group("date_part('year', created_at), date_part('month', created_at)").count("created_at")

answered Feb 13, 2013 at 9:48

RAJ

9,7471 gold badge36 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nishutosh Sharma Over a year ago

And I think if we were to use this query in mysql, then this won't work. My idea is to use uattachment.link_counters.find_by_sql("sql query here") But thanks for the answer man. Thanks a lot, it really helped.

Collectives™ on Stack Overflow

Mysql query not working in postgresql in rails 3.2.11

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related