Re: White space affecting parsing of range values

On Wed, 6 May 2020 at 17:30, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 5/6/20 9:19 AM, Thom Brown wrote:
> > On Wed, 6 May 2020 at 17:13, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> >>
> >> On 5/6/20 9:00 AM, Thom Brown wrote:
> >>> Hi,
> >>>
> >>> I noticed I'm getting an error when adding white space to a numeric
> >>> range. I can run this:
> >>>
> >>> postgres=# SELECT 5::numeric <@ '(,10]'::numrange;
> >>> ?column?
> >>> ----------
> >>> t
> >>> (1 row)
> >>>
> >>> But I can't run this:
> >>>
> >>> postgres=# SELECT 5::numeric <@ '( ,10]'::numrange;
> >>> ERROR: invalid input syntax for type numeric: " "
> >>> LINE 1: SELECT 5::numeric <@ '( ,10]'::numrange;
> >>> ^
> >>> If one had constructed a series of ranges, and wanted to line them up
> >>> vertically for easy comprehension, this wouldn't be possible.
> >>>
> >>> This doesn't seem to be a problem with regular numeric values:
> >>>
> >>> postgres=# SELECT ' 3 '::numeric;
> >>> numeric
> >>> ---------
> >>> 3
> >>> (1 row)
> >>>
> >>>
> >>> Shouldn't white space be ignored in range values?
> >>>
> >>
> >> https://www.postgresql.org/docs/12/rangetypes.html
> >>
> >> "Whitespace is allowed before and after the range value, but any
> >> whitespace between the parentheses or brackets is taken as part of the
> >> lower or upper bound value. (Depending on the element type, it might or
> >> might not be significant.)
> >> "
> >
> > I guess I should read the docs more carefully. Shouldn't this be
> > insignificant for a numeric value?
>
> No:
>
> select ' '::numeric;
> ERROR: invalid input syntax for type numeric: " "
> LINE 1: select ' '::numeric;
>
> >
> >> SELECT 5::numeric <@ '(00,10]'::numrange;
> >> ?column?
> >> ----------
> >> t
> >
> > Your example isn't equivalent to mine. That sets a lower bound.
>
> SELECT 5::numeric <@ numrange(NULL ,10, '(]');
> ?column?
> ----------
> t

Yes, I guess the numrange function would be a decent substitute in this case.

> From previous link:
>
> "-- Using NULL for either bound causes the range to be unbounded on that
> side.
> SELECT numrange(NULL, 2.2);"

--
Thom