Postgresql WITH statement invalid on Heroku

Question

I've got a Rails find_by_sql method that works fine locally, in console and the statement also directly in Postgresql, but the statement causes an ActiveRecord::StatementInvalid error when i deploy it to Heroku.

I'm running Postgresql version 9.0.3 locally and using a shared db on Heroku on their Cedar stack.

The error i'm getting is:

PG::Error: ERROR: syntax error at or near "WITH normal_items" LINE 1: WITH normal_items AS (SELECT normal_items_month, count(id)... ^ : WITH normal_items AS (SELECT normal_items_month, count(id) as normal_items_total FROM (SELECT date_trunc('month',created_at) as normal_items_month, id from items WHERE items.a_foreign_key_id IS NULL) z group by normal_items_month), special_items AS (SELECT special_items_month, count(id) as special_items_total FROM (SELECT date_trunc('month',created_at) as special_items_month, id from items WHERE items.a_foreign_key_id IS NOT NULL) x group by special_items_month ) SELECT to_char(month, 'fmMon') as month, coalesce(normal_items_total, 0) as normal_items_total, coalesce(special_items_total, 0) as special_items_total FROM (select generate_series(min(normal_items_month), max(normal_items_month), '1 month'::interval) as month FROM normal_items) m LEFT OUTER JOIN normal_items ON normal_items_month = month LEFT OUTER JOIN special_items ON special_items_month = month

For readability the statement is:

WITH normal_items AS (SELECT normal_items_month, count(id) as normal_items_total 
                      FROM (SELECT date_trunc('month',created_at) as normal_items_month, id from items 
                            WHERE items.a_foreign_key_id IS NULL) z 
                      group by normal_items_month), 
    special_items AS (SELECT special_items_month, count(id) as special_items_total 
                      FROM (SELECT date_trunc('month',created_at) as special_items_month, id from items 
                            WHERE items.a_foreign_key_id IS NOT NULL) x 
                      group by special_items_month ) 
SELECT 
to_char(month, 'fmMon') as month, 
coalesce(normal_items_total, 0) as normal_items_total, 
coalesce(special_items_total, 0) as special_items_total 
FROM (select generate_series(min(normal_items_month), max(normal_items_month), '1 month'::interval) as month FROM normal_items) m 
LEFT OUTER JOIN normal_items ON normal_items_month = month 
LEFT OUTER JOIN special_items ON special_items_month = month

This just a provides me with some stats to use with Google Charts, the output is:

Jun 178 0
Jul 0   0
Aug 72  0
Sep 189 0
Oct 24  0
Nov 6   0
Dec 578 0
Jan 0   0
Feb 0   0
Mar 89  0
Apr 607 0
May 281 0
Jun 510 0
Jul 190 0
Aug 0   0
Sep 501 0
Oct 409 0
Nov 704 0

BluesRockAddict · Accepted Answer · 2012-05-08 21:56:06Z

3

Heroku's shared plan runs PostgreSQL 8.3 which doesn't support WITH keyword (it was introduced in PostgreSQL 8.4).

If you upgrade to Heroku's dedicated database package, you'll be able to use PostgreSQL 9.1.

answered May 8, 2012 at 21:56

BluesRockAddict

15.6k3 gold badges40 silver badges35 bronze badges

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

Comments

John Beynon · Accepted Answer · 2012-05-08 22:06:25Z

2

The default Heroku shared DB is Postgres 8.3 - you can use 9.1 in the public beta of the Shared DB plan - more details at https://postgres.heroku.com/blog/past/2012/4/26/heroku_postgres_development_plan/.

For production you can make use of the newly announced Crane plan at $50 per month https://postgres.heroku.com/blog/past/2012/4/26/heroku_postgres_development_plan/

answered May 8, 2012 at 22:06

John Beynon

37.5k8 gold badges92 silver badges97 bronze badges

1 Comment

Pete Over a year ago

Cheers John. Tempted to try the public beta of 9.1, but given the only thing this particular statement is for is metrics i think i might hold back for the time being until i have enough time to thoroughly test everything.

Collectives™ on Stack Overflow

Postgresql WITH statement invalid on Heroku

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related