postgres列别名问题 [英] postgres column alias problem

问题描述

作为Postgresql的新手（我正在移动，因为我把我的网站移动到只支持它的heroku，我不得不重构我的一些查询和代码。这是一个我不能完全相信的问题理解问题：

  PGError：错误：列l_user_id不存在
 LINE 1：... t_id其中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作为l_geopoint_id 
从位置l 
离开连接位置l2在l.geopoint_id = l2.geopoint_id 
其中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显然不喜欢订单条款s未选择字段。但是我现在得到的错误使得它看起来像我也没有走样。任何有postgres经验的人都会看到这个问题吗？

我可能会遇到一堆这些问题 - 查询在mySql中正常工作...

解决方案

在PostgreSQL中，您不能使用带有别名的表达式。只有简单的别名在那里工作。您的查询应该如下所示：

  select distinct 
 l2。*，
 l.user_id as l_user_id ，
 l.geopoint_id作为l_geopoint_id 
从位置l 
离开连接位置l2 on l.geopoint_id = l2.geopoint_id 
其中l.user_id = 8 
 order by l2 .geopoint_id，l.user_id = l2.user_id desc; 
  

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

这是PostgreSQL-general邮件列表中的相关消息。以下内容位于 的文档中ORDER BY 子句：

每个表达式可以是名称或
输出
列 （SELECT列表项）的序数，或者
可以是一个任意表达式，由 input-column 值。

使用表达式时没有别名。

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 ...
                                                             ^

...query:

   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

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?

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

解决方案

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;

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

This is relevant message on PostgreSQL-general mailing list. The following is in the documentation of ORDER BY clause:

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

So no aliases when expression used.

这篇关于postgres列别名问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！