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. ;-)
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 |