PostgreSQL - column doesn't exist (WHERE with AS)

Question

i am a bit newbie to PostgreSQL, but i have a few experiences with MySQL.

The Postgres is showing me and error - COLUMN doesn't exist, but this is a "virtual column", created by AS.

Code, which is working very well in MySQL:

  SELECT place.*, 3956 * 2 * ASIN(SQRT( POWER(SIN((place.lattitude - $1) * pi() / 180 / 2), 2) + COS($2 * pi() / 180) * COS(place.lattitude * pi() / 180) *POWER(SIN(($3 - place.longitude) * pi() / 180 / 2), 2) )) AS "distance" FROM place WHERE place.longitude BETWEEN $4 AND $5 AND place.lattitude BETWEEN $6 AND $7 HAVING "distance" < $8 ORDER BY "distance" LIMIT 10

But Postgres is showing Column "distance" does not exist.

How can I rewrite it? (Please write full SQL query, not "How to")

$number is variable (against SQL injection)

Thanks

score 2 · Accepted Answer · answered Jun 26 '17 at 10:48

Use a subquery:

SELECT p.*
FROM (SELECT place.*, 3956 * 2 * ASIN(SQRT( POWER(SIN((place.lattitude - $1) * pi() / 180 / 2), 2) + COS($2 * pi() / 180) * COS(place.lattitude * pi() / 180) *POWER(SIN(($3 - place.longitude) * pi() / 180 / 2), 2) )) AS "distance"
      FROM place
      WHERE place.longitude BETWEEN $4 AND $5 AND
            place.lattitude BETWEEN $6 AND $7
     ) p
WHERE "distance" < $8
ORDER BY "distance"
LIMIT 10;

You don't want to use a subquery in MySQL because it materializes the intermediate result. Other databases are smarter in how they optimize queries, and do not necessarily materialize subqueries.

PostgreSQL - column doesn't exist (WHERE with AS)

1 Answers1