5

I am trying to calculate distance between two coordinates and fetching some information based on a few conditions. A similar query with the 'having' clause was working in MySQL. But why is it not working in PostgreSQL?

This is my query:

    SELECT *, ( 6371 * acos( cos( radians(latitude) ) * cos( radians( latitude1 ) ) *
    cos( radians( longitude1 ) - radians(longitude) ) + sin( radians(latitude) ) *
    sin( radians( latitude1 ) ) ) ) AS distance from table WHERE
    verified=true AND best_for LIKE '%xyz%' AND uuid NOT IN (SELECT uuid::uuid FROM table2 WHERE
    from_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date AND
    to_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date  AND )
    HAVING distance < 50 ORDER BY distance

Why does distance not exist? And how can I modify this query to make it work in PostgreSQL?

Peter Mortensen
  • 349
  • 2
  • 10
Lokesh Pandey
  • 197
  • 3
  • 10

1 Answers1

7

That's the rule the SQL standard sets (and MySQL ignores some of the rules the standard defines and allows invalid SQL).

You can't use a column alias on the same level where you defined it and having is only allowed in a query that uses aggregation. If you want to avoid repeating the expression, use a derived table.

It's also typically faster to use NOT EXISTS instead of NOT IN

SELECT *
FROM (
  SELECT *, 
        ( 6371 * acos( cos( radians(latitude) ) * cos( radians( latitude1 ) ) * 
          cos( radians( longitude1 ) - radians(longitude) ) + sin( radians(latitude) ) * 
          sin( radians( latitude1 ) ) ) ) AS distance 
  from table t1
  WHERE verified = true 
    AND best_for LIKE '%xyz%' 
    AND NOT EXISTS (SELECT *
                     FROM table2 t2
                     WHERE t2.from_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date 
                       AND t2.to_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date  
                       AND t2.uuid::uuid = t1.uuid)
) x                       
WHERE distance < 50 
ORDER BY distance