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

On 06/08/2018 10:23 AM, Alexey Dokuchaev wrote:
> 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

Not always, bigints are just the default. All those cases where folks
use the serial 'type' are getting an int sequence:

create table serial_test2(id serial);

\d serial_test2
Table "public.serial_test2"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+------------------------------------------
id | integer | | not null |
nextval('serial_test2_id_seq'::regclass)

\d+ serial_test2_id_seq
Sequence "public.serial_test2_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.serial_test2.id

> 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?

Not sure, someone else will have to explain.
>
> ./danfe
>
> P.S. On a tangentally related note, why is "NO CYCLE" is the default
> for sequences?

My guess is because sequences are often used to provide numbers for a
PRIMARY KEY and NO CYCLE is a heads up for key duplication before the PK
code kicks in.

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

Where in the docs are you seeing this?

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com