PostgreSQL: Using subquery abbreviation ('AS') in the WHERE clause

postgresql, sql

Solution

You could use a subquery:

SELECT a,b,c,d FROM
    (SELECT 
        a, b, 
        (A VERY LONG AND COMPLICATED SUBQUERY) AS c,
        (ANOTHER VERY LONG AND COMPLICATED SUBQUERY) AS d
        FROM table
    ) AS T1
WHERE c AND d

You could also do this with a CTE.

Problem

Consider the following query in PostgreSQL: ``` SELECT a, b, (A VERY LONG AND COMPLICATED SUBQUERY) AS c, (ANOTHER VERY LONG AND COMPLICATED SUBQUERY) AS d FROM table ``` I want to have `c` and `d` in the `WHERE` clause, like: ``` WHERE c AND d; ``` But, as far as I know, I can only do: ``` WHERE A VERY LONG AND COMPLICATED SUBQUERY) AND (ANOTHER VERY LONG AND COMPLICATED SUBQUERY) ``` Which is clumsy, code-replicating, breaking the single-choice principle and utterly ugly. By the way, the same problem applies to the `SELECT` clause: I can not use abbreviations for previously-defined subqueries.

Original source