Postgresql WHERE与age()函数 [英] Postgresql WHERE with age() function
问题描述
我很确定这已经被问过了,但是我正在努力为包含数据的表获取正确的语法
I'm pretty sure this has been asked before but I am struggling to get the correct syntax for a table containing data like
id date type report item_id
1 2018-11-07 Veröffentlichung des 9-Monats-Berichtes TRUE 16
2 2018-11-06 Veröffentlichung des 9-Monats-Berichtes TRUE 17
3 2019-03-07 Veröffentlichung des Jahresberichtes TRUE 17
4 2019-05-10 Bericht zum 1. Quartal TRUE 17
我要表达的查询是
SELECT date, AGE(now(), date) as t1
FROM dates
WHERE t1 > 0
意思是我只是在寻找过去的值。
但是,我收到错误消息
Meaning I am only looking for values in the past.
However, I get an error
错误:列 t1不存在
ERROR: column "t1" does not exist
(当然,这是一个别名)。 PostgreSQL此处不支持别名吗?
(of course, it is an alias). Does Postgresql not support aliases here?
推荐答案
您不能在 WHERE $ c $中引用别名c>条件,因为在
SELECT
之前在逻辑上执行了 WHERE
。
You cannot refer to alias in WHERE
condition, because logically WHERE
is executed before SELECT
.
您可以使用子查询:
SELECT *
FROM (SELECT date, AGE(now(), date) as t1
FROM dates) sub
WHERE sub.t1 > interval '0::seconds';
或 LATERAL
(我最喜欢的方式):
Or LATERAL
(my favourite way):
SELECT date, s.t1
FROM dates
,LATERAL (SELECT AGE(now(), date) as t1) AS s
WHERE s.t1 > interval '0::seconds';
或重复表达(违反 DRY原理):
SELECT date, AGE(now(), date) as t1
FROM dates
WHERE AGE(now(), date) > interval '0::seconds';
关于计算 AGE
的方法确实需要它,因为您可以将其重写为 date> now()
。
As for calculating AGE
you don't really need it, because you could rewrite it as date > now()
.
相关文章:
为什么 linq to sql查询与常规SQL查询不同,为什么以FROM关键字开头?
这篇关于Postgresql WHERE与age()函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!