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@xxxxxxxxxxx
