Postgres how to implement calculated column with clause Postgres how to implement calculated column with clause sql sql

Postgres how to implement calculated column with clause


If you don't want to repeat the expression, you can use a derived table:

select *from (   select id, cos(id) + cos(id) as op    from myTable ) as t WHERE op > 1;

This won't have any impact on the performance, it is merely syntactic sugar required by the SQL standard.

Alternatively you could rewrite the above to a common table expression:

with t as (  select id, cos(id) + cos(id) as op   from myTable )select *from t where op > 1;

Which one you prefer is largely a matter of taste. CTEs are optimized in the same way as derived tables are, so the first one might be faster especially if there is an index on the expression cos(id) + cos(id)


select id, (cos(id) + cos(id)) as op from selfies WHERE (cos(id) + cos(id)) > 1

You should specify the calculation in the where clause as you can't use a alias.