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