0

I have the following sql query that works fine in MYSQL but it gives me an error in Postgres:

SELECT * , 
        ( 3959 * acos( cos( radians('13.1') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('53.1') ) + sin( radians('13.1') ) * sin(radians(lat)) ) ) AS distance 
FROM person 
HAVING distance < 10 
ORDER BY distance LIMIT 20
**ERROR**:  column "distance" does not exist
LINE 1: ...(radians(lat)) ) ) AS distance FROM person HAVING distance <...
2
  • The distance between two points can be calculated a lot easier using PostGIS or the extension earthdistance
    – user1822
    Jan 13, 2020 at 20:53
  • You can't use an alias in a HAVING clause in PostgreSQL or SQL Server - MySQL does this as an "extension" to the standard. Are you using MySQL < 5.7.5? If ONLY_FULL_GROUP_BY is set, it won't work in MySQL either - see the links here.
    – Vérace
    Jan 13, 2020 at 22:06

2 Answers 2

1

You can repeat the expression in the filter condition, or you can avoid that by using a subquery:

SELECT *
FROM (SELECT *, 
             3959 * acos(
                       cos(
                          radians('13.1')
                       ) * cos(
                          radians(lat)
                       ) * cos(
                          radians(lng) - radians('53.1')
                       ) + sin(
                          radians('13.1')
                       ) * sin(radians(lat))
                    ) AS distance 
      FROM person) AS q 
WHERE distance < 10 
ORDER BY distance LIMIT 20;
0

Taking a_horse_with_no_name's comment to a coded answer, simply repace your HAVING keyword with WHERE:

SELECT * FROM
    (SELECT 
            *, 
            ( 3959 * acos( cos( radians('13.1') ) 
            * cos( radians( lat ) ) * cos( radians( lng ) 
            - radians('53.1') ) + sin( radians('13.1') ) 
            * sin(radians(lat)) ) 
            ) AS distance 
    FROM person) t
WHERE distance < 10 
ORDER BY distance LIMIT 20
6
  • You can't use a column alias in the WHERE clause
    – user1822
    Jan 13, 2020 at 22:08
  • @a_horse_with_no_name - can you use CTEs in postgresql? If so, I'll re-write that way. If not, then you could reproduce the column definition in the where clause. I hate that, but not every DBMS supports referencing aliases in the WHERE clause, unfortunately :/ Jan 13, 2020 at 22:11
  • yes, of course you can use CTEs, but that's not necessary for this. A simple derived table will do just fine. select * from ( select .., as distance) t where distance < 10 ...
    – user1822
    Jan 13, 2020 at 22:20
  • The reason why Postgres (and other DBMS) don't support to use an alias in the WHERE clause is that this isn't defined in the SQL standard
    – user1822
    Jan 13, 2020 at 22:21
  • Cheers. Have adjusted. Jan 13, 2020 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.