CODEWITHSUNDEEP
mysqlsqlnode.jspostgresql
CZ workman
CZ workman

Reputation: 195

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

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

Upvotes: 1

Views: 545

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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.

Upvotes: 2

Related Questions