Postgresql WHERE with age() function

Question

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

The query I am trying to formulate is

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

ERROR: column "t1" does not exist

(of course, it is an alias). Does Postgresql not support aliases here?

Answer 1

You cannot refer to alias in WHERE condition, because logically WHERE is executed before SELECT.

You could use subquery:

SELECT * 
FROM (SELECT date, AGE(now(), date) as t1
      FROM dates) sub
WHERE sub.t1 > interval '0::seconds';

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';

Or repeat expression(violates DRY principle):

SELECT date, AGE(now(), date) as t1
FROM dates
WHERE AGE(now(), date) > interval '0::seconds';

As for calculating AGE you don't really need it, because you could rewrite it as date > now().

---

Related articles:

PostgreSQL: using a calculated column in the same query

MySQL - Search into a custom Column

Why do “linq to sql” queries starts with the FROM keyword unlike regular SQL queries?

Answer 2

If you're in a hurry and have an (ordered) index on date don't do that.

Because this query can use the index giving a massive gain in performance at only a slight investment in coding effort.

SELECT date, AGE(now(), date) AS t1
FROM dates
WHERE date > now();

I say now(), because you did, but perhaps you want CURRENT_DATE instead

To create a suitable index do

create index dates_date on dates(date);