PostgreSQL: Using a Subquery Paragraph ("AS") in a WHERE Clause

Question

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 , for example:

 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) 

What is inconvenient, copies, violates the principle of a single choice and is completely ugly.

By the way, the same problem applies to the SELECT : I cannot use abbreviations for previously defined subqueries.

Answer 1

You can 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 can also do this with CTE .