问题描述

作为 Postgresql 的新手(我搬过来是因为我将我的网站移到只支持它的 heroku，我不得不重构我的一些查询和代码.这是一个我不能完全理解的问题理解问题:

As a newbie to Postgresql (I'm moving over because I'm moving my site to heroku who only support it, I'm having to refactor some of my queries and code. Here's a problem that I can't quite understand the problem with:

PGError: ERROR:  column "l_user_id" does not exist
LINE 1: ...t_id where l.user_id = 8 order by l2.geopoint_id, l_user_id ...
                                                             ^

...查询:

   select distinct 
          l2.*, 
          l.user_id as l_user_id, 
          l.geopoint_id as l_geopoint_id 
     from locations l 
left join locations l2 on l.geopoint_id = l2.geopoint_id 
    where l.user_id = 8 
 order by l2.geopoint_id, l_user_id = l2.user_id desc

子句l.user_id as l_user_id, l.geopoint_id as l_geopoint_id"被添加，因为显然 postgres 不喜欢没有选择字段的 order 子句.但是我现在得到的错误看起来好像我也没有走样.任何有 postgres 经验的人都看到了这个问题吗?

clause "l.user_id as l_user_id, l.geopoint_id as l_geopoint_id" was added because apparently postgres doesn't like order clauses with fields not selected. But the error I now get makes it look like I'm also not getting aliasing. Anybody with postgres experience see the problem?

我可能会遇到很多这样的问题——查询在 mySql 中运行良好...

I'm likely to have a bunch of these problems -- the queries worked fine in mySql...

推荐答案

在 PostgreSQL 中，您不能在 order by 中使用带有别名的表达式.只有普通别名在那里工作.您的查询应如下所示:

In PostgreSQL you can not use expression with an alias in order by. Only plain aliases work there. Your query should look like this:

   select distinct 
          l2.*, 
          l.user_id as l_user_id, 
          l.geopoint_id as l_geopoint_id 
     from locations l 
left join locations l2 on l.geopoint_id = l2.geopoint_id 
    where l.user_id = 8 
 order by l2.geopoint_id, l.user_id = l2.user_id desc;

我假设您的意思是 l2.user_id=l.user_id 应该先行.

I assume you mean that l2.user_id=l.user_id ought to go first.

这是 PostgreSQL 通用邮件列表上的相关消息.以下是ORDER BY文档中的内容/code> 子句: