Thread: INTEGER range ("-2147483648" is not accepted.)
Hi all, I've found a bit strange thing on the INTEGER range in the official manual. http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html According to the official manual, the INTEGER range is "-2147483648 to +2147483647". However, my example in below shows that "-2147483648" is not accepted. Is this correct? Any suggestions? Regards, --------------------------------------------------------------------- template1=# SELECT version(); version ------------------------------------------------------------------------------------------------------------ PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 32-bit (1 row) template1=# SELECT -2147483647::integer; ?column? ------------- -2147483647 (1 row) template1=# SELECT -2147483648::integer; ERROR: integer out of range template1=# SELECT +2147483648::integer; ERROR: integer out of range template1=# SELECT +2147483647::integer; ?column? ------------ 2147483647 (1 row) template1=# --------------------------------------------------------------------- -- NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>
2010/6/22 Satoshi Nagayasu <satoshi.nagayasu@gmail.com>: > Hi all, > > I've found a bit strange thing on the INTEGER range in the official manual. > > http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html > > According to the official manual, the INTEGER range is "-2147483648 to +2147483647". > However, my example in below shows that "-2147483648" is not accepted. > > Is this correct? Any suggestions? > > Regards, > > --------------------------------------------------------------------- > template1=# SELECT version(); > version > ------------------------------------------------------------------------------------------------------------ > PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 32-bit > (1 row) > > template1=# SELECT -2147483647::integer; > ?column? > ------------- > -2147483647 > (1 row) > > template1=# SELECT -2147483648::integer; > ERROR: integer out of range > template1=# SELECT +2147483648::integer; > ERROR: integer out of range > template1=# SELECT +2147483647::integer; > ?column? > ------------ > 2147483647 > (1 row) > > template1=# > --------------------------------------------------------------------- > Hmm... yes, that's not what I'd expect either: postgres=# SELECT -32768::smallint; ERROR: smallint out of range postgres=# SELECT -9223372036854775808::bigint; ERROR: bigint out of range I think those min values are all out by 1. Thom
On Tue, Jun 22, 2010 at 10:27 AM, Satoshi Nagayasu <satoshi.nagayasu@gmail.com> wrote: > Hi all, > > I've found a bit strange thing on the INTEGER range in the official manual. > > http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html > > According to the official manual, the INTEGER range is "-2147483648 to +2147483647". > However, my example in below shows that "-2147483648" is not accepted. > > Is this correct? Any suggestions? > > template1=# SELECT -2147483648::integer; > ERROR: integer out of range This gets parsed as "cast 2147483648 to integer, then take it negative". Which overflows, because it can only go up to 2147483647. What you want is: postgres=# select (-2147483648)::integer; int4 ------------- -2147483648 (1 row) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On 22 June 2010 09:44, Magnus Hagander <magnus@hagander.net> wrote: > On Tue, Jun 22, 2010 at 10:27 AM, Satoshi Nagayasu > <satoshi.nagayasu@gmail.com> wrote: >> Hi all, >> >> I've found a bit strange thing on the INTEGER range in the official manual. >> >> http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html >> >> According to the official manual, the INTEGER range is "-2147483648 to +2147483647". >> However, my example in below shows that "-2147483648" is not accepted. >> >> Is this correct? Any suggestions? >> >> template1=# SELECT -2147483648::integer; >> ERROR: integer out of range > > This gets parsed as "cast 2147483648 to integer Why? And if so, it would probably be more useful if the error message was something more like: ERROR: integer 2147483648 out of range That would at least show the user what the value was seen as by the parser. Thom
On 22 June 2010 09:59, Satoshi Nagayasu <satoshi.nagayasu@gmail.com> wrote: > Magnus, > > Thanks for your advice. I've understood how it happens. > > However, it looks tricky and difficult to understand, > so I hope that the message could be more understandable > as Thom mentioned. > > Regards, > This does appear to be a gotcha, as the following returns a negative integer as expected: postgres=# SELECT -2147483648; ?column? ------------- -2147483648 (1 row) postgres=# SELECT pg_typeof(-2147483648); pg_typeof ----------- integer (1 row) And just in case... postgres=# SELECT pg_typeof(test.my_num) FROM (SELECT -2147483648) AS test(my_num); pg_typeof ----------- integer (1 row) So it's affected by the cast operator? Thom
On 22 June 2010 10:46, Thom Brown <thombrown@gmail.com> wrote: > On 22 June 2010 09:59, Satoshi Nagayasu <satoshi.nagayasu@gmail.com> wrote: >> Magnus, >> >> Thanks for your advice. I've understood how it happens. >> >> However, it looks tricky and difficult to understand, >> so I hope that the message could be more understandable >> as Thom mentioned. >> >> Regards, >> > > This does appear to be a gotcha, as the following returns a negative > integer as expected: > > postgres=# SELECT -2147483648; > ?column? > ------------- > -2147483648 > (1 row) > > postgres=# SELECT pg_typeof(-2147483648); > pg_typeof > ----------- > integer > (1 row) > > And just in case... > > postgres=# SELECT pg_typeof(test.my_num) FROM (SELECT -2147483648) AS > test(my_num); > pg_typeof > ----------- > integer > (1 row) > > So it's affected by the cast operator? > > Thom > Actually, come to think of it, shouldn't we have a gotchas page on the wiki? Thom
Thom, > Actually, come to think of it, shouldn't we have a gotchas page on the wiki? I agree with that it should be described in some tech document, but I don't have any good idea where/how it should be written. Basically, it's a parser issue, but app developers may meet it on their type casting (my guess), and it's a bit tricky. Regards, On 2010/06/22 18:57, Thom Brown wrote: > On 22 June 2010 10:46, Thom Brown<thombrown@gmail.com> wrote: >> On 22 June 2010 09:59, Satoshi Nagayasu<satoshi.nagayasu@gmail.com> wrote: >>> Magnus, >>> >>> Thanks for your advice. I've understood how it happens. >>> >>> However, it looks tricky and difficult to understand, >>> so I hope that the message could be more understandable >>> as Thom mentioned. >>> >>> Regards, >>> >> >> This does appear to be a gotcha, as the following returns a negative >> integer as expected: >> >> postgres=# SELECT -2147483648; >> ?column? >> ------------- >> -2147483648 >> (1 row) >> >> postgres=# SELECT pg_typeof(-2147483648); >> pg_typeof >> ----------- >> integer >> (1 row) >> >> And just in case... >> >> postgres=# SELECT pg_typeof(test.my_num) FROM (SELECT -2147483648) AS >> test(my_num); >> pg_typeof >> ----------- >> integer >> (1 row) >> >> So it's affected by the cast operator? >> >> Thom >> > > Actually, come to think of it, shouldn't we have a gotchas page on the wiki? > > Thom > -- NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>
Magnus, Thanks for your advice. I've understood how it happens. However, it looks tricky and difficult to understand, so I hope that the message could be more understandable as Thom mentioned. Regards, On 2010/06/22 17:48, Thom Brown wrote: > On 22 June 2010 09:44, Magnus Hagander<magnus@hagander.net> wrote: >> On Tue, Jun 22, 2010 at 10:27 AM, Satoshi Nagayasu >> <satoshi.nagayasu@gmail.com> wrote: >>> Hi all, >>> >>> I've found a bit strange thing on the INTEGER range in the official manual. >>> >>> http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html >>> >>> According to the official manual, the INTEGER range is "-2147483648 to +2147483647". >>> However, my example in below shows that "-2147483648" is not accepted. >>> >>> Is this correct? Any suggestions? >>> >>> template1=# SELECT -2147483648::integer; >>> ERROR: integer out of range >> >> This gets parsed as "cast 2147483648 to integer > > Why? And if so, it would probably be more useful if the error message > was something more like: > ERROR: integer 2147483648 out of range > > That would at least show the user what the value was seen as by the parser. > > Thom > -- NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>
On Tue, Jun 22, 2010 at 09:36:30AM +0100, Thom Brown wrote: > 2010/6/22 Satoshi Nagayasu <satoshi.nagayasu@gmail.com>: > > Hi all, > > > > I've found a bit strange thing on the INTEGER range in the official manual. > > > > http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html > > > > According to the official manual, the INTEGER range is "-2147483648 to +2147483647". > > However, my example in below shows that "-2147483648" is not accepted. > > > > Is this correct? Any suggestions? > > > > Regards, > > > > --------------------------------------------------------------------- > > template1=# SELECT version(); > > version > > ------------------------------------------------------------------------------------------------------------ > > PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 32-bit > > (1 row) > > > > template1=# SELECT -2147483647::integer; > > ?column? > > ------------- > > -2147483647 > > (1 row) > > > > template1=# SELECT -2147483648::integer; > > ERROR: integer out of range > > template1=# SELECT +2147483648::integer; > > ERROR: integer out of range > > template1=# SELECT +2147483647::integer; > > ?column? > > ------------ > > 2147483647 > > (1 row) > > > > template1=# > > --------------------------------------------------------------------- > > > > Hmm... yes, that's not what I'd expect either: > > postgres=# SELECT -32768::smallint; > ERROR: smallint out of range > postgres=# SELECT -9223372036854775808::bigint; > ERROR: bigint out of range > > I think those min values are all out by 1. Nope. Same problem. SELECT (-32768)::smallint; -32768 SELECT (-9223372036854775808)::bigint; -9223372036854775808 I agree that the appropriate error message should complain about the actual error, which is that 32768, or 2147483648, or 9223372036854775808, as the case may be, is out of range in the positive direction. Possibly the "hint" might mention that :: binds tighter than - does. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On 23 June 2010 00:07, David Fetter <david@fetter.org> wrote: > On Tue, Jun 22, 2010 at 09:36:30AM +0100, Thom Brown wrote: >> 2010/6/22 Satoshi Nagayasu <satoshi.nagayasu@gmail.com>: >> > Hi all, >> > >> > I've found a bit strange thing on the INTEGER range in the official manual. >> > >> > http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html >> > >> > According to the official manual, the INTEGER range is "-2147483648 to +2147483647". >> > However, my example in below shows that "-2147483648" is not accepted. >> > >> > Is this correct? Any suggestions? >> > >> > Regards, >> > >> > --------------------------------------------------------------------- >> > template1=# SELECT version(); >> > version >> > ------------------------------------------------------------------------------------------------------------ >> > PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 32-bit >> > (1 row) >> > >> > template1=# SELECT -2147483647::integer; >> > ?column? >> > ------------- >> > -2147483647 >> > (1 row) >> > >> > template1=# SELECT -2147483648::integer; >> > ERROR: integer out of range >> > template1=# SELECT +2147483648::integer; >> > ERROR: integer out of range >> > template1=# SELECT +2147483647::integer; >> > ?column? >> > ------------ >> > 2147483647 >> > (1 row) >> > >> > template1=# >> > --------------------------------------------------------------------- >> > >> >> Hmm... yes, that's not what I'd expect either: >> >> postgres=# SELECT -32768::smallint; >> ERROR: smallint out of range >> postgres=# SELECT -9223372036854775808::bigint; >> ERROR: bigint out of range >> >> I think those min values are all out by 1. > > Nope. Same problem. > > SELECT (-32768)::smallint; > -32768 > > SELECT (-9223372036854775808)::bigint; > -9223372036854775808 > > I agree that the appropriate error message should complain about the > actual error, which is that 32768, or 2147483648, or > 9223372036854775808, as the case may be, is out of range in the > positive direction. Possibly the "hint" might mention that :: binds > tighter than - does. > Is that the right behaviour though? Shouldn't the signed value reach the cast step rather than the absolute value? Or maybe Postgres could implicitly accept -12345::integer to be (-12345)::integer. Is there a blocking reason as to why it must work this way? Am I asking too many questions? Was that last question necessary? Thom
Thom Brown <thombrown@gmail.com> writes:
> Is that the right behaviour though? Shouldn't the signed value reach
> the cast step rather than the absolute value? Or maybe Postgres could
> implicitly accept -12345::integer to be (-12345)::integer. Is there a
> blocking reason as to why it must work this way?
Yes. There is no reason to assume that - means the same thing for every
datatype. In general, :: should (and does) bind tighter than *every*
operator, to ensure that the appropriately typed operator is applied.
regards, tom lane
On 23 June 2010 02:49, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Thom Brown <thombrown@gmail.com> writes: >> Is that the right behaviour though? Shouldn't the signed value reach >> the cast step rather than the absolute value? Or maybe Postgres could >> implicitly accept -12345::integer to be (-12345)::integer. Is there a >> blocking reason as to why it must work this way? > > Yes. There is no reason to assume that - means the same thing for every > datatype. In general, :: should (and does) bind tighter than *every* > operator, to ensure that the appropriately typed operator is applied. > Okay. I'll admit that this won't be a common case, but could the error message make reference to the value it took? Thom
On 22 June 2010 18:49, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Thom Brown <thombrown@gmail.com> writes: >> Is that the right behaviour though? Shouldn't the signed value reach >> the cast step rather than the absolute value? Or maybe Postgres could >> implicitly accept -12345::integer to be (-12345)::integer. Is there a >> blocking reason as to why it must work this way? > > Yes. There is no reason to assume that - means the same thing for every > datatype. In general, :: should (and does) bind tighter than *every* > operator, to ensure that the appropriately typed operator is applied. > Sorry for adding to the non-DOC drift, but why is - assumed to be a unary operator on an unsigned integer, rather than parsed as part of an integer? Integers have digits with an optional - or + prefix (not unary operators). E.g., ([+\-]?[0-9]+) -Mike
On Wed, Jun 23, 2010 at 10:29 AM, Mike Toews <mwtoews@gmail.com> wrote: > On 22 June 2010 18:49, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Thom Brown <thombrown@gmail.com> writes: >>> Is that the right behaviour though? Shouldn't the signed value reach >>> the cast step rather than the absolute value? Or maybe Postgres could >>> implicitly accept -12345::integer to be (-12345)::integer. Is there a >>> blocking reason as to why it must work this way? >> >> Yes. There is no reason to assume that - means the same thing for every >> datatype. In general, :: should (and does) bind tighter than *every* >> operator, to ensure that the appropriately typed operator is applied. >> > > Sorry for adding to the non-DOC drift, but why is - assumed to be a > unary operator on an unsigned integer, rather than parsed as part of > an integer? Integers have digits with an optional - or + prefix (not > unary operators). E.g., ([+\-]?[0-9]+) You can't assume that a dash followed by digits is always a negative number. Consider: SELECT 10-4; If you we interpret this as "10" followed by "-4", it's a syntax error. You have to treat it as a separate token and work out later whether it's a binary operator or a prefix operator. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company