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