Oddity with literal intervals

Mike Mascari <mascarm@mascari.com> writes:

Here's something odd I stumbled upon:

[estore@lexus] select now() - '1 day';
ERROR: invalid input syntax for type timestamp with time zone: "1 day"

What's odd about it? The preferred interpretation is timestamptz minus
another timestamptz (yielding an interval). If you want timestamptz
minus interval (yielding timestamptz), you have to do something to cue
the system that the literal should be taken as an interval.

The "+" cases work because there is no timestamp plus timestamp operator.

regards, tom lane

Tom Lane wrote:

Mike Mascari <mascarm@mascari.com> writes:

Here's something odd I stumbled upon:

[estore@lexus] select now() - '1 day';
ERROR: invalid input syntax for type timestamp with time zone: "1 day"

What's odd about it? The preferred interpretation is timestamptz minus
another timestamptz (yielding an interval). If you want timestamptz
minus interval (yielding timestamptz), you have to do something to cue
the system that the literal should be taken as an interval.

The "+" cases work because there is no timestamp plus timestamp operator.

Okay. That's why I posted it to -general and not -bugs, because I
suspected there was some reason behind it. I guess it seemed odd
because it has been on rare occasion that I have encountered types
where there exists a '-' operator without a corresponding '+'
operator. And, coincidentally having a '+' operator available for
timestampz + interval just added to my confusion. But the above
makes perfect sense.

Thanks!

Mike Mascari