Thanks. That
On Thu, Jun 29, 2023 at 2:24 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Braiam <braiamp@gmail.com> writes:
> > On Thu, Jun 29, 2023 at 1:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> There is no age(date, date) function. What we have is age(timestamp,
> >> timestamp) and age(timestamptz, timestamptz), so the parser has to
> >> choose which type to coerce to --- and it prefers timestamptz.
>
> > According to \df+ age both timestamptz and timestamp are immutable:
>
> True, but not very relevant: it's the coercion from date that's
> giving you trouble.
>
> > So, whatever type is coerced into pre-function evaluation comes
> > with strange results. I'm not aware of a way that I can see what
> > kind of type is being coerced into.
>
> EXPLAIN will show that, eg
>
> =# explain verbose select age(current_date, current_date);
> QUERY PLAN
>
> ---------------------------------------------------------------------------------------------------
> Result (cost=0.00..0.02 rows=1 width=16)
> Output: age((CURRENT_DATE)::timestamp with time zone, (CURRENT_DATE)::timestamp with time zone)
> (2 rows)
Thanks. Then this is still wrong.
=# explain verbose select age('2022-02-01'::date, '2022-01-01'::date);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Result (cost=0.00..0.02 rows=1 width=16)
Output: age(('2022-02-01'::date)::timestamp with time zone,
('2022-01-01'::date)::timestamp with time zone)
(2 rows)
Function age(timestamp with time zone, timestamp with time zone) is
marked as immutable. Postgres shouldn't complain about it.
> regards, tom lane
--
Braiam