Thread: BUG #12872: Inconsistent processing of interval values

The following bug has been logged on the website:

Bug reference:      12872
Logged by:          Jan Mate
Email address:      jan.mate@inf-it.com
PostgreSQL version: 9.4.1
Operating system:   Debian GNU/Linux 7
Description:

Postgresql 9.4 documentation says:

Interval values can be written using the following verbose syntax:

[@] quantity unit [quantity unit...] [direction]

but it works ONLY if a unit is used exactly once, e.g.:

testdb=> SELECT (now()-' +5 days -12 hours -1 second +1 week'::interval) AS
tstz;
             tstz
-------------------------------
 2015-03-05 11:36:08.646603+01
(1 row)

works, but:

testdb=> SELECT (now()-' +5 days -12 hours -1 second +1 day'::interval) AS
tstz;
ERROR:  invalid input syntax for type interval: " +5 days -12 hours -1
second +1 day"
LINE 1: SELECT (now()-' +5 days -12 hours -1 second +1 day'::interva...

generates an error. Yes, I understand that writing '+5 days +1 day' is
"stupid" but it conforms the syntax in the documentation.


JM

On Mon, Mar 16, 2015 at 11:03:52PM +0000, jan.mate@inf-it.com wrote:
> works, but:
>
> testdb=> SELECT (now()-' +5 days -12 hours -1 second +1 day'::interval) AS
> tstz;
> ERROR:  invalid input syntax for type interval: " +5 days -12 hours -1
> second +1 day"
> LINE 1: SELECT (now()-' +5 days -12 hours -1 second +1 day'::interva...
>
> generates an error. Yes, I understand that writing '+5 days +1 day' is
> "stupid" but it conforms the syntax in the documentation.

Well, I think we have always had that restriction, and you are the first
person to mention it.  Are you thinking we should document this
limitation?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

Hi Bruce,

> On 17 Mar 2015, at 20:45, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Mon, Mar 16, 2015 at 11:03:52PM +0000, jan.mate@inf-it.com wrote:
>> works, but:
>>
>> testdb=> SELECT (now()-' +5 days -12 hours -1 second +1 day'::interval) AS
>> tstz;
>> ERROR:  invalid input syntax for type interval: " +5 days -12 hours -1
>> second +1 day"
>> LINE 1: SELECT (now()-' +5 days -12 hours -1 second +1 day'::interva...
>>
>> generates an error. Yes, I understand that writing '+5 days +1 day' is
>> "stupid" but it conforms the syntax in the documentation.
>
> Well, I think we have always had that restriction, and you are the first
> person to mention it.  Are you thinking we should document this
> limitation?

yes, document it or allow to use the same unit multiple times. The documentation
fix looks as the easier solution ...


JM