Column does not exist (creating a column in a query)

Question

I'm getting the error 'column "distance" does not exist' in postgres at the SELECT part, running this query. 'distance' is meant to be the distance between 2 points given coordinates. I suspect this is a timing issue? The function is created properly before the error occurs.

CREATE OR REPLACE FUNCTION pg_temp.earthDistance(lat1 double precision, lng1 double precision, lat2 double precision, lng2 double precision)
  RETURNS double precision AS
$BODY$
SELECT 
  asin(
  sqrt(
    sin(radians($3-$1)/2)^2 +
    sin(radians($4-$2)/2)^2 *
    cos(radians($1)) *
    cos(radians($3))
  )
  ) * 7918 AS distance;
$BODY$
  LANGUAGE sql IMMUTABLE;

SELECT populated_place.name AS populated_place_name, 
  feature.name AS feature_name, 
  ROUND(
    pg_temp.earthDistance(
      populated_place.latitude,
      populated_place.longitude,
      feature.latitude,
      feature.longitude)::NUMERIC,
  2) AS distance,
  RANK() OVER (PARTITION BY populated_place_name ORDER BY distance) AS rank
FROM populated_place JOIN feature ON
  feature.type='summit' AND
  populated_place.population>=100000
WHERE distance<=200
ORDER BY populated_place_name, rank;

score 2 · Accepted Answer · edited May 23 '17 at 11:44

You cannot use an alias (distance) in WHERE clause. Use a derived table:

SELECT 
    *, 
    RANK() OVER (PARTITION BY populated_place_name ORDER BY distance) AS rank
FROM (  
    SELECT 
        populated_place.name AS populated_place_name, 
        feature.name AS feature_name, 
        ROUND(
            pg_temp.earthDistance(
            populated_place.latitude,
            populated_place.longitude,
            feature.latitude,
            feature.longitude)::NUMERIC, 2) AS distance
    FROM populated_place 
    JOIN feature ON
        feature.type='summit' AND
        populated_place.population>=100000
    ) sub
WHERE distance <= 200
ORDER BY populated_place_name, rank;

See also: Using an Alias in a WHERE clause.

Column does not exist (creating a column in a query)

1 Answers1