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

Alexey Dokuchaev <danfe(at)nsu(dot)ru> writes:
> What is the rationale for (int ^ int) to return double precision rather
> than numeric? I am missing something obvious here?

There are two ^ operators, one taking float8 and one taking numeric.
Since float8 is the preferred datatype in the numeric category
(i.e. the top of the implicit-casting hierarchy), the float8 operator
will be chosen unless one of the earlier disambiguation rules applies:

https://www.postgresql.org/docs/current/static/typeconv-oper.html

In this case, you need at least one input to be numeric, so that
rule 3c fires before rule 3d can.

You might argue that numeric should be the preferred type, but that
falls foul of the SQL standard, which is quite clear that only
numeric -> float8 can be an implicit cast, not the other direction.
(They word it in terms of casts between exact and approximate
numeric types, but that's the outcome.) The type resolution
heuristics break down pretty badly if the preferred type in a
category doesn't have implicit casts from every other type in the
category.

BTW, you could also just write 2.0^63 to get numeric.

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

(a) the SQL standard says so, (b) most people tend to expect serial
columns to not repeat values.

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

We didn't get around to implementing that till v10.

regards, tom lane