PostgreSQL column does not exist when sub select

user_10858915 0
Viewed 56

I have a table called user, and I need to get another users in an expecific distance range and the distance between them. My query is this:

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

I need to recover the user list and the distance, but the "as distance" is not working:

ERROR:  column "distance" does not exist.

I tried with:

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

However, the error remains the same.

postgresql
1 Answers

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.

user_146600
Related