Question 246990 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – column "name" does not exist

I am having issue with ALIAS variable "distance" in my PSQL query. (*please ignore that I am using voutecount as distance)

SELECT rentalid, createdDate, votecount AS distance
FROM rental 
WHERE longitude=? AND latitude=?   
HAVING distance < 25 ORDER BY distance LIMIT 0 OFFSET 30

The error is "distance does not exist" but I have defined distance already so I cannot tell what the issue is.

nested exception is org.postgresql.util.PSQLException: ERROR: column "distance" does not exist
  Position: 107] with root cause
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    @Maimoona Abid Okay I am not getting any error but the actual SQL command I am running is as below and I am getting any empty resultset. I believe it is because the query is actually searching RENTAL Table for longitude and latitude that I give as parameter instead of using the parameter for calculating the distance.

    public List<RentalDto> selectRentalsByDistance(Double lon, Double lat) {
        var sql = """
                SELECT *
                FROM (
                    SELECT rentalid, createdDate, 
                    ( 3959 * acos( cos(radians(37) ) * cos( radians(latitude) ) * cos( radians(longitude) - radians(-122) )
                                    + sin( radians(37) ) * sin( radians(latitude) )) ) as distance
                    FROM rental
                    WHERE lon = ? AND lat = ?                                    
                ) AS subquery
                WHERE distance < 25
                ORDER BY distance
                LIMIT 30 OFFSET 0
                """;
        return jdbcTemplate.query(sql, rentalDtoRowMapper, new Object[] {lon, lat});
    }

  2. 0

    Try this approach, first create a subquery that calculates the distance alias and then filter and order the results in the outer query.

    SELECT *
FROM (
    SELECT rentalid, createdDate, votecount AS distance
    FROM rental 
    WHERE longitude = ? AND latitude = ?
) AS subquery
WHERE distance < 25
ORDER BY distance
LIMIT 30 OFFSET 0;

    Hope it works 🙂

    Login or Signup to reply.
  3. 0

    You get this error because you are using HAVING on an alias distance that you defined in the SELECT clause and this is not supported. So you have to use the WHERE clause instead of HAVING to filter rows based on the distance alias;

    SELECT rentalid, createdDate, votecount AS distance
FROM rental 
WHERE longitude = ? AND latitude = ? AND votecount < 25
ORDER BY distance
LIMIT 0 OFFSET 30;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search