postgres column alias problem

问题:

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

回答1:

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.

回答2:

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_id part (move it to where if that's one of the join conditions) and it should work.

Update Below select (with = l2.user_id removed) should work just fine. I've tested it (with different table / column names, obviously) on 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

回答3:

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:

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:

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:

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.

回答4:

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

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

Just plain untrue.

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

id

30 10 20 50 40 (5 rows)