Postgresql WHERE with age() function [duplicate]

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?

also t1 (interval) is not comparable with 0 (number)

Jasen
– Jasen

2018-09-30 10:14:00 +00:00
Commented Sep 30, 2018 at 10:14 — Jasen
– Jasen, Commented Sep 30, 2018 at 10:14

Lukasz Szozda · Accepted Answer · 2018-09-30 12:17:03Z

3

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().

5 Comments

Jasen Over a year ago

don't use a subquery. CTEs are optimisation fences and that doesn't help here.

Jan Over a year ago

COuld you elaborate on LATERAL a bit? And I'm indeed always trying to be DRY.

Jan Over a year ago

Now I get HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Lukasz Szozda Over a year ago

@Jan Sure, updated my answer

Jan Over a year ago

@LukaszSzozda: Thanks!

Jasen · Accepted Answer · 2018-10-02 05:59:28Z

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

edited Oct 2, 2018 at 5:59

answered Sep 30, 2018 at 10:15

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

1 Comment

Jan Over a year ago

Thanks for the answer - how would I change the table to have an index?

Collectives™ on Stack Overflow

Postgresql WHERE with age() function [duplicate]

2 Answers 2

5 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Linked

Related