PostgreSQL查询在本地（9.x）工作，但在Heroku（8.x）上给出语法错误 [英] PostgreSQL query works locally (9.x), but gives syntax error on Heroku (8.x)

问题描述

我开发了自己的网站，它在我的本地计算机上就像一个魅力。该查询由Stackoverflow的其他成员在此线程中提供。

I developed my site and it works like a charm on my local machine. The query is supplied by a fellow Stackoverflow member in this thread.

相关的schema.rb部分

本地数据与Heroku上的数据相同。

Same data locally as on Heroku.

select  *
from    (
        select  *
        ,       row_number() over (partition by odds_type order by odds_index desc) as rn2
        from    (
                select  *
                ,       row_number() over (partition by event_id, bookmaker_id, odds_type 
                                           order by created_at desc) as rn1
                from    Odds
                where   event_id = #{e.id}
                ) sub1
        where   rn1 = 1
        ) sub2
where   rn2 = 1

缺点中的错误ole

Odds Load (88.4ms)  select * from ( select *, row_number() over (partition by odds_type order by odds_index desc) as rn2 from (select *, row_number() over (partition by event_id, bookmaker_id, odds_type order by created_at desc) as rn1 from Odds where event_id = 21 ) sub1 where rn1 = 1 ) sub2where rn2 = 1
ActiveRecord::StatementInvalid: PG::Error: ERROR:  syntax error at or near "over"
LINE 1: select * from ( select *, row_number() over (partition by od...
                                           ^

推荐答案

row_number（）和窗口函数通常在PostgreSQL 8.4或更高版本中可用。该错误表明您正在尝试在旧版本（8.3？）上执行此查询，因此无法正常工作。

row_number() and window functions in general are available in PostgreSQL 8.4 or higher. The error indicates that you're trying this query on an older version (8.3?), so it can't work.

更新：在研究了原始的问题和答案之后使用PG 9.x，我相信您可以通过PostgreSQL特定的 DISTINCT ON 子句，非标准，但在较早版本的PG中可用，在您的情况下非常方便。这是我的建议：

Update: after studying the original question and answer with PG 9.x, I believe you could obtain the same result with the PostgreSQL-specific DISTINCT ON clause, non-standard but available in older versions of PG and quite handy in your case. Here's my proposal:

SELECT DISTINCT on (ot) bi,ot,oi FROM
  (select distinct on (bi,ot) bi,ot,oi from odds
    where ei=1 order by bi,ot,created_at desc) subq
 ORDER BY ot,oi DESC;

这篇关于PostgreSQL查询在本地（9.x）工作，但在Heroku（8.x）上给出语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！