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

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.klaver@aklaver.com