postgresql bigint too big?

Question

Running

PostgreSQL 11.4 (Debian 11.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

I tried this statement:

SELECT CAST (2^63-1 as bigint);

but got an unexpected error message:

bigint out of range

Oddly, if I just replace the exponent form with its integer equivalent:

SELECT CAST (9223372036854775807 as bigint)

It works as expected. I suppose it's just me not understanding things properly. FWIW the largest number in exponent notation I could use is this:

SELECT CAST (2^63-513 as bigint);

Anything larger errored the same way.

What am I missing about how PostgreSQL does it's exponentiation? Or, is it being converted to float and back and I'm seeing rounding/truncation errors?

Answer 1

Yes, it's being converted to a double precision, so you're seeing those rounding errors:

select pg_typeof(2^63);
    pg_typeof
------------------
 double precision

select pg_typeof(2^63-1);
    pg_typeof
------------------
 double precision

It works if you start with a numeric:

select (2::numeric^63-1)::bigint;
        int8
---------------------
 9223372036854775807

Answer 2

power is not an integer operation. It works with either returns a double or numeric.

select cast(2^63-1 as bigint);

Expands out as

select cast( (power(2, 63) - 1) as bigint );

power(2, 63) here returns a double which at that size has an imprecision of about 512.

If you instead start with numeric it will use numeric.

select (power(2::numeric, 63) - 1)::bigint;
            int8         
---------------------
 9223372036854775807