sql - PostgreSQL reusing computation result in select query

Question

For example using SQL I can do:

SELECT (a+b) as c FROM table WHERE c < 5 AND (c*c+t) > 100;

Is there any way to do that using Postgres?

Answer 1

You cannot do that. Neither in PostgreSQL nor in standard SQL. The manual:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

One could debate the standard in this regard. Hugh Darwen goes to great lengths doing that in this article I was recently referred to.

You can use a subquery like Federico suggested, or a Common Table Expression (CTE) like this:

WITH cte AS (SELECT a + b AS c FROM x)
SELECT c
FROM   cte
WHERE  c < 5
AND    (c*c+t) > 100;

CTEs are especially useful for more complex operations or if you want to reuse the interim result in multiple (sub-)queries.