Re: timestamp issue

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> On 17.02.2011 08:06, Sachin Srivastava wrote:
>> postgres=# SELECT now() - '7 days' AS week_behind;
>> ERROR: invalid input syntax for type timestamp with time zone: "7 days"
>> Now, if '+' operator works with '7 days', why dint the '-' operator? Is this intentional or a bug?

> Intentional, or at least coincidental.

> postgres=# SELECT oprname, oprleft::regtype, oprright::regtype FROM
> pg_operator WHERE oprleft ='timestamptz'::regtype AND oprname IN('+', '-');
> oprname | oprleft | oprright
> ---------+--------------------------+--------------------------
> + | timestamp with time zone | interval
> - | timestamp with time zone | timestamp with time zone
> - | timestamp with time zone | interval
> (3 rows)

> With '-', it's getting interpreted as timestamptz-timestamptz.

To be more specific: since you didn't cast the unknown-type literal to
any particular type, the parser had to guess which operator is meant.
In a case like this it will prefer to guess that both operands are of
the same datatype. So, knowing that now() yields timestamptz, it tried
to interpret the literal as timestamptz too.

There is no corresponding problem on the + side because there's no
timestamptz+timestamptz operator, that not being a meaningful operation.

regards, tom lane