Thread: White space affecting parsing of range values
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?
--
Thom
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.) " SELECT 5::numeric <@ '(00,10]'::numrange; ?column? ---------- t -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, 6 May 2020 at 17:13, Adrian Klaver <adrian.klaver@aklaver.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? > SELECT 5::numeric <@ '(00,10]'::numrange; > ?column? > ---------- > t Your example isn't equivalent to mine. That sets a lower bound. Thom
On 5/6/20 9:19 AM, Thom Brown wrote: > On Wed, 6 May 2020 at 17:13, Adrian Klaver <adrian.klaver@aklaver.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 From previous link: "-- Using NULL for either bound causes the range to be unbounded on that side. SELECT numrange(NULL, 2.2);" > > Thom > -- Adrian Klaver adrian.klaver@aklaver.com
Thom Brown <thom@linux.com> writes:
> I guess I should read the docs more carefully. Shouldn't this be
> insignificant for a numeric value?
That would require the range code to know whether the subtype considers
whitespace significant (or perhaps more usefully, whether an all-spaces
input is valid). We've stayed away from requiring range_in to have any
type-specific knowledge of that sort.
Still, you could argue that the rule ought to be "an empty or all-blank
value must be quoted to distinguish it from an omitted bound" rather than
"an empty value must be quoted to distinguish it from an omitted bound".
I'm not sure if we could get away with redefining that at this point,
though. It looks like range_out quotes such values already, so maybe a
change wouldn't be totally catastrophic (in the sense of breaking dump
files). But I still suspect there would be more people unhappy than
happy.
regards, tom lane
On Wed, 6 May 2020 at 17:30, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 5/6/20 9:19 AM, Thom Brown wrote: > > On Wed, 6 May 2020 at 17:13, Adrian Klaver <adrian.klaver@aklaver.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
On Wed, 6 May 2020 at 17:33, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Thom Brown <thom@linux.com> writes: > > I guess I should read the docs more carefully. Shouldn't this be > > insignificant for a numeric value? > > That would require the range code to know whether the subtype considers > whitespace significant (or perhaps more usefully, whether an all-spaces > input is valid). We've stayed away from requiring range_in to have any > type-specific knowledge of that sort. > > Still, you could argue that the rule ought to be "an empty or all-blank > value must be quoted to distinguish it from an omitted bound" rather than > "an empty value must be quoted to distinguish it from an omitted bound". > > I'm not sure if we could get away with redefining that at this point, > though. It looks like range_out quotes such values already, so maybe a > change wouldn't be totally catastrophic (in the sense of breaking dump > files). But I still suspect there would be more people unhappy than > happy. Okay, I see that this isn't really worth changing. It's surprising behaviour, but I can see it's not a huge issue, and can be worked around anyway. Thanks -- Thom