Re: Bug #484: TIMESTAMP arithmetic insconsistencies

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