Why Can't I Use Aliases in the WHERE Clause of a PostgreSQL Query?

Referencing Alias in WHERE Clause: An Exploration

In PostgreSQL (psql), it's not directly possible to reference aliases in the WHERE clause due to the execution order. The WHERE clause is executed before aliases are applied, leading to the error "column "_year" does not exist."

This occurs because the WHERE clause operates on the original column names, and the alias is only applied later, during the result set generation. Therefore, the alias is not recognized when the WHERE clause is executed.

To resolve this issue, the query must be rewritten to use the original column name directly in the WHERE clause. For example, consider the query:

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

This query will fail with the error "column "_year" does not exist." To fix it, the alias must be replaced with the original column name in the WHERE clause:

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

The above is the detailed content of Why Can't I Use Aliases in the WHERE Clause of a PostgreSQL Query?. For more information, please follow other related articles on the PHP Chinese website!