sql - Reference alias in WHERE clause

Question

In psql, trying to reference alias in WHERE clause:

SELECT
    SUBSTRING(pk, 6, 2)::INT AS _year
FROM
    listing
WHERE
    _year > 90

it causes error:

ERROR:  column "_year" does not exist
LINE 1: ...STRING (pk, 6, 2)::INT AS _year FROM listing WHERE _year > 90...
                                                              ^
********** Error **********

ERROR: column "_year" does not exist

Is it possible to reference alias in WHERE clause?

Answer 1

This is not possible as in sql, the order of execution is first, the where clause and then the select. At the time where clause is getting executed, it does not know what you have defined as an alias and so you will get that error.

You need to rewrite your query like this..

SELECT
    SUBSTRING(pk, 6, 2)::INT AS _year
FROM
    listing
WHERE
SUBSTRING(pk, 6, 2)::INT > 90