vedar vedar - 6 months ago 24
SQL Question

Reference alias in WHERE clause

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

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