tok tok - 1 year ago 46
SQL Question

Referencing calculated value in SQL

I wonder why this doesn't work (in PostgreSQL):

create table t(c integer);
insert into t values(1);
insert into t values(2);
insert into t values(3);

select c+10 as result from t group by result order by result;
--> OK
select c+10 as result from t where result > 10;
--> ERROR: column "result" does not exist

So I can refer to column result in group by and in order by, but not in where. Why is that?

This is just a simplified example. In the real thing there is a function in select-part. I need the result of that function in where-part. I wouldn't like to calculate the function twice. What would be the best way to do this (as for the example above, how to avoid calculate c+10 twice)?


You could use LATERAL:

SELECT result 
,LATERAL (SELECT c+10) AS s(result)  -- here you calculate what you need
WHERE result > 10;

Then you could refer to calculated value in SELECT/WHERE/ORDER BY... clauses.


Simplified execution order:

enter image description here

Image from:

As you see WHERE is before SELECT that is why you cannot refer to calculated expression in WHERE.