How to fix Postgres error column does not exist?

Question

I have the following query to postgress db

SELECT id,
       address, 
       dblong,  
       dblat,  
       111* DEGREES(ACOS(COS(RADIANS(latpoint)) * COS(RADIANS(dblat)) *  
                         COS(RADIANS(longpoint) - RADIANS(dblong)) +  
                         SIN(RADIANS(latpoint)) * SIN(RADIANS(dblat)))) AS distance_in_km 
FROM  
    (SELECT id, 
            address, 
            MAX(longitude) AS dblong,
            MAX(latitude) AS dblat  
     FROM doorbots  
     WHERE created_at > '%s' AND created_at < '%s'  
     GROUP BY 1) AS s  
JOIN 
  (SELECT %s AS latpoint, %s AS longpoint) AS p ON 1=1  
WHERE distance_in_km < %s  
GROUP BY 1;

I don't understand how to fix the following error: column "distance_in_km" does not exist?

just use the whole formula instead of alias in where clause, or use CTE or subquery before Where to use alias — Vao Tsun
– Vao Tsun, Commented Mar 13, 2017 at 11:03

Vao Tsun · Accepted Answer · 2017-03-13 11:04:57Z

that should work:

with q as (
SELECT id,
       address, 
       dblong,  
       dblat,  
       111* DEGREES(ACOS(COS(RADIANS(latpoint)) * COS(RADIANS(dblat)) *  
                         COS(RADIANS(longpoint) - RADIANS(dblong)) +  
                         SIN(RADIANS(latpoint)) * SIN(RADIANS(dblat)))) AS distance_in_km 
FROM  
    (SELECT id, 
            address, 
            MAX(longitude) AS dblong,
            MAX(latitude) AS dblat  
     FROM doorbots  
     WHERE created_at > '%s' AND created_at < '%s'  
     GROUP BY 1) AS s  
JOIN 
  (SELECT %s AS latpoint, %s AS longpoint) AS p ON 1=1  
)
select * from q
WHERE distance_in_km < %s  
GROUP BY 1;

Collectives™ on Stack Overflow

How to fix Postgres error column does not exist?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related