postgresql - 子选择时PostgreSQL列不存在

Question

我有一个名为 user 的表，我需要在特定距离范围内获取其他用户以及他们之间的距离。我的查询是这样的：

select ( 6371 * acos( 
                cos( radians(users.latitude) )
              *    cos( radians(-22.9035) )
              * cos( radians(-43.2096) - radians(users.longitude) )
              + sin( radians(users.latitude) )
              * sin( radians(-22.9035) ) 
              ) 
    ) as distance, users from users where users.id != 41 and distance > 50

我需要恢复用户列表和距离，但“距离”不起作用：

ERROR:  column "distance" does not exist. 

我试过：

with distance as (
select ( 6371 * acos( 
                cos( radians(users.latitude) )
              * cos( radians(-22.9035) )
              * cos( radians(-43.2096) - radians(users.longitude) )
              + sin( radians(users.latitude) )
              * sin( radians(-22.9035) ) 
              ) 
    ) from users
)
select * from users where users.id != 41 and distance > 50

但是，错误仍然相同。

标签： postgresql

Answer 1

In Postgres, you cannot use column aliases in a where clause. That's why your first query does not work.

Your second query using a CTE can be made to work, but distance is a virtual table, not a column. Join with it.

with users_distance as (
  select
    id,
    ( 6371 * acos( 
                cos( radians(users.latitude) )
              * cos( radians(-22.9035) )
              * cos( radians(-43.2096) - radians(users.longitude) )
              + sin( radians(users.latitude) )
              * sin( radians(-22.9035) ) 
              ) 
    ) as distance
  from users
)
select *
from users
join users_distance ud on users.id = ud.id
where users.id != 41 and ud.distance > 50

If you do this a lot, consider adding a generated column to the table so you don't have to recalculated it all the time.

alter table users add column distance numeric
  generated always as (
    6371 * acos( 
                cos( radians(users.latitude) )
              * cos( radians(-22.9035) )
              * cos( radians(-43.2096) - radians(users.longitude) )
              + sin( radians(users.latitude) )
              * sin( radians(-22.9035) ) 
              )
  ) stored

Try it

Finally, rather than doing these calculations yourself, consider using the very powerful PostGIS extension.