Why does an alias with a 'having' clause not exist in PostgreSQL?

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?

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

StackExchange Database Administrators Q#281438, answer score: 7