Hello I am having problem with postgresql lateral join. My query is this:

SELECT *
FROM foo
LEFT JOIN LATERAL (SELECT tsrange(now() at time zone 'utc', expiration_date) as time_range)
                   ON (expiration_date > (now() at time zone 'utc') AND type = 'FOO') 

It fails with “[22000] ERROR: range lower bound must be less than or equal to range upper bound” as if the result of the lateral join was evaluated before the ON condition.

I am trying to get all results and for those with expiration date > now() calculate the range so filtering in the main query is not an option.

I tried to rewrite the query with WHERE condition in the lateral join but it didnt help.

I would expect that the “lateral join” would be evaluated only for the rows matching the conditions so the error should never happen since expiration date > now().

Any idea how to fix this?

Postgresql version 16.3