Vtgyjfy

Till now I used the following syntax to add an interval to a timestamp:

select now() + '5 year';

This worked fine till I tried to subtract the interval which results in a syntax error.

invalid input syntax for type timestamp with time zone: "5 year"

LINE 1: select now() - '5 year'

In the documentation I learned that the syntax actually is:

select now() - interval '5 year'

So my questions are:
Why does select now() + '5 year' work at all?
Does it work only by accident and it might break in a future Postgresql release?

Like @a_horse explained, there are two operators available for the expression now() - '5 year':

• 
  now() returns timestamp with time zone (timestamptz).


• 
  '5 year' is an untyped string literal.

SELECT oprleft::regtype, oprname, oprright::regtype

FROM   pg_operator

WHERE  oprname = '-'

AND    oprleft = 'timestamptz'::regtype;



         oprleft          | oprname |         oprright         

--------------------------+---------+--------------------------

 timestamp with time zone | -       | timestamp with time zone

 timestamp with time zone | -       | interval

(2 rows)

The exact reason for the choice can be found in the manual in the chapter Operator Type Resolution:

[...]

 2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of
operators considered), use it. [...]

    a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. [...]

[...]

Bold emphasis mine. Read the whole chapter to understand the process fully.

The same type is preferred if one argument type is unknown and a matching operator is available. There is an operator for timestamptz - timestamptz, bingo. The operator is resolved here. Fortunately, '5 years' is illegal input for timestamptz, else this might result in confusion!

The operator resolves to timestamptz - interval after adding an explicit type cast:

now() - interval '5 year'  -- always the way to go

My guess(!) is:

The + operator for timestamps only supports adding an interval (timestamp + interval). And thus it's clear that the string value '5 year' needs to be (implicitly) converted to an interval

The - operator on the other hand supports two different combinations:

• 
  timestamp - timestamp


• 
  timestamp - interval.

Apparently Postgres prefers to use the timestamp - timestamp option and tries to (implicitly) convert '5 year' to a timestamp which of course fails.