(2^63 - 1)::bigint => out of range? (because of the double precision)

From: Alexey Dokuchaev <danfe(at)nsu(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: (2^63 - 1)::bigint => out of range? (because of the double precision)
Date: 2018-06-08 17:23:23
Message-ID: 20180608172323.GA32312@regency.nsu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi there,

I've decided to run some tests to see how my tables' ids would survive
when their yielding sequences would start hitting their MAXVALUE's, by
doing some "SELECT setval('foo_id_seq', ~maxbigint)". As I don't like
to hardcode numbers (esp. huge numbers, because sequences are always[*]
bigint's), I've tried to use (2^63 - 1)::bigint as "maxbigint", to no
avail, in contrast to (2^31 - 1)::int (-> below is short mnemonic for
"returns"):

select (2^31 - 1)::int -> 2147483647 (correct)

select (2^63 - 1)::bigint -> bigint out of range (???)
select (9223372036854775807)::bigint -> 9223372036854775807 (correct)

Apparently, this is because the type of 2^63 is double precision, which
is inexact; if I explicitly cast any of 2 or 63 to ::numeric, it behaves
as expected:

select (2::numeric^63 - 1)::bigint -> 9223372036854775807 (ok)
select (2^63::numeric - 1)::bigint -> 9223372036854775807 (ditto)

What is the rationale for (int ^ int) to return double precision rather
than numeric? I am missing something obvious here?

./danfe

P.S. On a tangentally related note, why is "NO CYCLE" is the default
for sequences?

[*] Per documentation, "The [SQL] standard's AS <data type> expression
is not supported." Another "why is it so?" question, btw. ;-)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2018-06-08 17:30:45 Re: (2^63 - 1)::bigint => out of range? (because of the double precision)
Previous Message Robert Creager 2018-06-08 16:39:21 Re: Query hitting empty tables taking 48 minutes