pgsql-bugs@postgresql.org writes:
> I have found some inconsistencies relating to TIMESTAMP arithmetic.
I dug into this a little, and what seems to be causing the inconsistency
is a surprising implicit coercion.
> select now()-'2001-09-30' where (now()-'2001-09-30') < 50;
>> returned 0 rows
I did this to see how the parser interpreted this expression:
regression=# create view v as select (now()-'2001-09-30') < 50;
CREATE
regression=# \d v
View "v"
Column | Type | Modifiers
----------+---------+-----------
?column? | boolean |
View definition: SELECT (reltime((now() - '2001-09-30 00:00:00-04'::timestamptz)) < (50)::reltime);
Since now()-'2001-09-30' yields an interval, the choice of reltime to do
the comparison is not too surprising (apparently there's an
int4->reltime coercion but not int4->interval). Anyway the result is at
least somewhat sensible: the 50 gets interpreted as 50 seconds.
> select now()-'2001-09-30' where (now()-'2001-09-30') < 50.0;
>> "16 days xx:xx:xx"
regression=# drop view v;
DROP
regression=# create view v as select (now()-'2001-09-30') < 50.0;
CREATE
regression=# \d v
View "v"
Column | Type | Modifiers
----------+---------+-----------
?column? | boolean |
View definition: SELECT (text((now() - '2001-09-30 00:00:00-04'::timestamptz)) < '50'::text);
Text!!?? Well, apparently textlt is the only available operator whose
input types can be reached in one coercion step from both interval and
float8. But it seems a pretty surprising choice anyway. I wonder
whether we have too many implicit coercions to text available.
regards, tom lane