postgresql bigint too big?

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?

Solution

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