Обсуждение: INT8 / float casting question

psql.bin (9.3.4, server 9.3.5)
Type "help" for help.

dev=# select (2^63-1)::INT8;
ERROR:  bigint out of range





Does it HAVE to be so?


http://en.wikipedia.org/wiki/9223372036854775807



Thanks
Filip

On Fri, Oct 31, 2014 at 03:58:06PM +0100, Filip Rembiałkowski wrote:
> psql.bin (9.3.4, server 9.3.5)
> Type "help" for help.
> 
> dev=# select (2^63-1)::INT8;
> ERROR:  bigint out of range
> 
> 
> Does it HAVE to be so?
> 
> 
> http://en.wikipedia.org/wiki/9223372036854775807
> 
> Thanks
> Filip

Yes:

http://www.postgresql.org/docs/9.3/static/datatype.html

Table 8-1:

bigint, aliases int8 - signed eight-byte integer

PostgreSQL does not have an unsigned integer type, since all the
SQL ones are signed.

Regards,
Ken

On 10/31/2014 07:58 AM, Filip Rembiałkowski wrote:
> psql.bin (9.3.4, server 9.3.5)
> Type "help" for help.
>
> dev=# select (2^63-1)::INT8;
> ERROR:  bigint out of range
>

> Does it HAVE to be so?
>
>
> http://en.wikipedia.org/wiki/9223372036854775807

It seems to be not a problem with the number but the exponentiation and 
casting:

test=# create table bigint_test (int_fld int8);
CREATE TABLE
test=# insert into bigint_test values (9223372036854775807);
INSERT 0 1
test=# select * from bigint_test ;       int_fld
--------------------- 9223372036854775807

test=# select 2^63-1;       ?column?
---------------------- 9.22337203685478e+18
(1 row)

test=# select (2^63-1)::int8;
ERROR:  bigint out of range



>
>
>
> Thanks
> Filip
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/31/2014 07:58 AM, Filip Rembiałkowski wrote:
> psql.bin (9.3.4, server 9.3.5)
> Type "help" for help.
>
> dev=# select (2^63-1)::INT8;
> ERROR:  bigint out of range
>
>
>
>
>
> Does it HAVE to be so?

No, figured it out:

test=# select ((2^63-1)::numeric);       numeric
--------------------- 9223372036854780000

is rounding up.

To get what you want:

test=# select (2^63::numeric-1)::int8;        int8
--------------------- 9223372036854775807
(1 row)


>
>
> http://en.wikipedia.org/wiki/9223372036854775807
>
>
>
> Thanks
> Filip
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com