18

I need to filter by calculated column in postgres. It's easy with MySQL but how to implement with Postgres SQL ?

pseudocode:

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

Any SQL tricks ?

sharp
  • 857
  • 1
  • 9
  • 21
  • MySQL doesn't have CTEs but that's one way to get around repeating the expression. You can also use an inline view/derived table/nested table or whatever they call it in MySQL – shawnt00 Aug 24 '15 at 16:56
  • 1
    Related: http://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause –  Aug 24 '15 at 17:56

2 Answers2

23

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)

  • 1
    If indexes are important it might be a good idea to just use to an explicit range of id values between +/-1.0471975511965977461542144610932 radians. – shawnt00 Aug 24 '15 at 19:14
  • Is this more efficient than the below answer where the calculation is in the where clause? – fatfrog Feb 08 '22 at 15:24
8
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.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • For those that are interested, this solution is quite a bit faster than using the derived table above. – fatfrog Feb 08 '22 at 15:28