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()
returnstimestamp 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