MySQL postgres 列别名问题

postgres column alias problem

提问by Dan Donaldson

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:

作为 Postgresql 的新手（我搬过来是因为我将我的网站移到只支持它的 heroku，我不得不重构我的一些查询和代码。这是一个我不太明白的问题和：

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?

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

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

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

回答by Tometzky

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

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

   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_idought to go first.

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

This is relevant messageon PostgreSQL-general mailing list. The following is in the documentation of ORDER BYclause:

这是PostgreSQL 通用邮件列表上的相关消息。以下是子句的文档ORDER BY：

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

每个表达式可以是输出列（SELECT 列表项）的名称或序号，也可以是由输入列值构成的任意表达式。

So no aliases when expression used.

所以在使用表达式时没有别名。

回答by ChssPly76

You have:

你有：

order by l2.geopoint_id, l_user_id = l2.user_id desc

in your query. That's illegal syntax. Remove the = l2.user_idpart (move it to whereif that's one of the join conditions) and it should work.

在您的查询中。那是非法语法。移除= l2.user_id零件（where如果这是连接条件之一，则将其移至），它应该可以工作。

UpdateBelow select (with = l2.user_idremoved) should work just fine. I've tested it (with different table / column names, obviously) on Postgres 8.3

更新下方选择（= l2.user_id已删除）应该可以正常工作。我已经在 Postgres 8.3 上测试过它（显然使用不同的表/列名）

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 desc

回答by William Wagner

I ran into this same problem using functions from fuzzystrmatch - particularly the levenshtein function. I needed to both sort by the string distance, and filter results by the string distance. I was originally trying:

我使用fuzzystrmatch 中的函数遇到了同样的问题——尤其是levenshtein 函数。我需要按字符串距离排序，并按字符串距离过滤结果。我最初是在尝试：

SELECT thing.*, 
levenshtein(thing.name, '%s') AS dist 
FROM thing 
WHERE dist < character_length(thing.name)/2 
ORDER BY dist

But, of course, I got the error "column"dist" does not exist" from the WHERE clause. I tried this and it worked:

但是，当然，我从 WHERE 子句中得到了错误“列”dist“不存在”。我试过了，它奏效了：

SELECT thing.*, 
(levenshtein(thing.name, '%s')) AS dist 
FROM thing 
ORDER BY dist

But I needed to have that qualification in the WHERE clause. Someone else in this question said that the WHERE clause is evaluated before ORDER BY, thus the column was non-existent when it evaluated the WHERE clause. Going by that advice, I figured out that a nested SELECT statement does the trick:

但是我需要在 WHERE 子句中具有该限定条件。这个问题中的其他人说 WHERE 子句在 ORDER BY 之前进行评估，因此在评估 WHERE 子句时该列不存在。根据这个建议，我发现嵌套的 SELECT 语句可以解决问题：

SELECT * FROM 
(SELECT thing.*, 
     (levenshtein(thing.name, '%s')) AS dist 
     FROM thing 
     ORDER BY dist
) items 
WHERE dist < (character_length(items.name)/2)

Note that the "items" table alias is required and the dist column alias is accessible in the outer SELECT because it's unique in the statement. It's a little bit funky and I'm surprised that it has to be this way in PG - but it doesn't seem to take a performance hit so I'm satisfied.

请注意，“items”表别名是必需的，并且可以在外部 SELECT 中访问 dist 列别名，因为它在语句中是唯一的。这有点时髦，我很惊讶它在 PG 中必须如此 - 但它似乎并没有影响性能，所以我很满意。

回答by Richard Huxton

"was added because apparently postgres doesn't like order clauses with fields not selected"

“添加是因为显然 postgres 不喜欢未选择字段的订单子句”

"As far as order by goes - yes, PostgresQL (and many other databases) does not allow ordering by columns that are not listed in select clause."

“就 order by 而言 - 是的，PostgresQL（和许多其他数据库）不允许按 select 子句中未列出的列进行排序。”

Just plain untrue.

简直就是不真实。

=> SELECT id FROM t1 ORDER BY owner LIMIT 5;

=> SELECT id FROM t1 ORDER BY owner LIMIT 5;

id

ID

30 10 20 50 40 (5 rows)

30 10 20 50 40 (5 行)