Different syntax to add/substract interval

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.


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