Postgres如何实现带子句的计算列 [英] Postgres how to implement calculated column with clause

问题描述

我需要按postgres中的计算列进行过滤。使用MySQL很容易，但是如何使用Postgres SQL实施？

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

伪代码：

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

任何SQL技巧？

推荐答案

如果不想重复该表达式，则可以使用派生表：

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;

这不会对性能产生任何影响，只是SQL标准所需的语法糖。

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;

您更喜欢哪个，很大程度上取决于口味。 CTE的优化方法与派生表相同，因此第一个CTE可能会更快，特别是如果表达式 cos（id）+ cos（id）上有索引时

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)

这篇关于Postgres如何实现带子句的计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！