Geek Answers Handbook

Postgres how to implement a calculated column with a sentence

I need to filter a calculated column in postgres. Easy with MySQL, but how to implement it with Postgres SQL?

pseudo code:

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

Any SQL tricks?

+4
source share
2 answers

If you do not want to repeat the expression, you can use the view:

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

It will not affect performance, it is just the syntactic sugar required by the SQL standard.

Alternatively, you can rewrite the above expression into a general table expression:

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

Which one do you prefer depends a lot on taste. CTEs are optimized in the same way as views, so the former can be faster, especially if there is an expression indexcos(id) + cos(id)

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

where, alias.

+2

More articles:


All Articles