PostgreSQL query works locally but not on Heroku (same data)

Question

Here is query:

ActiveRecord::StatementInvalid (PGError: ERROR: syntax error at or near "over" select *, rank() over (partition by thread_i... ^

     SELECT *
     FROM (                                                                  
             select *, rank() over (partition by thread_id order by created_at DESC)
             from posts
             where circle_id IN (134) OR (receiver_id=3)
           ) as dt
     WHERE rank = 1

EDIT: here is a detailed explanation of what I am trying to do: Rails 3 app with PostgreSQL - Getting the list of messages grouped by converstation

It turns out the Heroku shared DB is a PostgreSQL version 8.3 therefore no windows function so the question become how can I do this query in PostgreSql 8.3?

THANKS!

The derived table solutions from Derek and Yahia should work with 8.3. There is a chance that posts.created_at = dt.maxdt could produce multiple results for a single thread though. You might want to include a client-side check to deal with duplicate thread_id,created_at pairs. The chance of a duplicate is quite low but "low" and "zero" aren't quite the same. — mu is too short
– mu is too short, Commented Sep 12, 2011 at 5:33
updated my answer with a possible solution for the duplicate problem... — Yahia
– Yahia, Commented Sep 12, 2011 at 5:42

Yahia · Accepted Answer · 2011-09-12 05:40:31Z

2

try

SELECT p.*
 FROM (                                                                  
         select x.thread_id, max(x.created_at) as maxdt
         from posts x
         where x.circle_id IN (134) OR x.receiver_id=3
         group by x.thread_id
       ) as dt
 INNER JOIN posts p ON p.thread_id = dt.thread_id and p.created_at = dt.maxdt 
 ORDER BY p.created_at DESC

EDIT - as per comment:

SELECT p.*
 FROM (                                                                  
         select x.thread_id, max(x.created_at) as maxdt, max (OID) maxo
         from posts x
         where x.circle_id IN (134) OR x.receiver_id=3
         group by x.thread_id
       ) as dt
 INNER JOIN posts p ON p.thread_id = dt.thread_id and p.created_at = dt.maxdt AND p.OID = dt.maxo
 ORDER BY p.created_at DESC

edited Sep 12, 2011 at 5:40

answered Sep 11, 2011 at 3:23

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

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

4 Comments

mu is too short Over a year ago

There is an exceedingly small chance that you'll get duplicates if Matteo manages to get multiply entries with the same thread_id,created_at pair. I'd deal with that possibility in Ruby though.

Yahia Over a year ago

see the edit above... it takes care of the possible duplicate problem by only returning the record with the highest OID (assuming it is the most current...) for a given combination of thread_id and created_at

mu is too short Over a year ago

Nice, the old oid trick. I'd give you another upvote for that but I only have one account :) Shame that the Heroku hasn't updated their shared DBs to 8.4 to get window functions. On the rare chance that there is a match I don't think it matters which one you pick, you just need a way to pick exactly one.

Matteo Melani Over a year ago

Guys thank you so much for your help!!! Both of you saved me so much time. Great job!

Derek · Accepted Answer · 2011-09-11 03:16:00Z

1

This should get you there:

 SELECT p.*
 FROM (                                                                  
         select thread_id, max(created_at) as maxdt
         from posts
         where circle_id IN (134) OR (receiver_id=3)
       ) as dt
 INNER JOIN posts p ON p.thread_id = dt.thread_id and p.created_at = dt.maxdt

answered Sep 11, 2011 at 3:16

Derek

23.4k8 gold badges55 silver badges61 bronze badges

1 Comment

mu is too short Over a year ago

There is an exceedingly small chance that you'll get duplicates if Matteo manages to get multiply entries with the same thread_id,created_at pair. I'd deal with that possibility in Ruby though.

Collectives™ on Stack Overflow

PostgreSQL query works locally but not on Heroku (same data)

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related