How Can I Correctly Use Aliases in PostgreSQL's WHERE Clause?

Referencing Aliases in WHERE Clauses in Postgres

In PostgreSQL, referring to aliases in the WHERE clause can lead to errors like "column does not exist." This occurs because the WHERE clause executes before aliases are defined in the SELECT clause.

Why it's Not Possible

In SQL, clauses are executed in a specific order: WHERE, FROM, GROUP BY, HAVING, SELECT, ORDER BY. This means that when Postgres encounters the WHERE clause, it does not yet know about any aliases you have defined in the SELECT clause.

Solution

To resolve this issue, you must rewrite your query to avoid using aliases in the WHERE clause. Instead, directly specify the expression to be compared. For example, the following query correctly selects rows where the second to sixth characters of the "pk" column, cast as an integer, are greater than 90:

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

Note: This limitation only applies to aliases defined in the SELECT clause. Aliases defined in other clauses, such as FROM or JOIN, can be used in subsequent clauses.

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