Re: INTEGER range ("-2147483648" is not accepted.)

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