Hello!

Why pgAdmin3 when added to the table fields id, type serial, creates a SEQUENCE with MAXVALUE equal to 9223372036854775807 ie under bigint. The id type exposes integer.

Whether it threatens with problems at achievement of type restriction integer?

Thanks for the answer!

1 answer 1

Interestingly, conducted an experiment:

 melkij=# create table testserial (id serial not null primary key, num integer not null); CREATE TABLE melkij=# alter sequence testserial_id_seq maxvalue 9223372036854775807 restart with 2147483646; ALTER SEQUENCE melkij=# insert into testserial (num) values(1); INSERT 0 1 melkij=# insert into testserial (num) values(2); INSERT 0 1 melkij=# insert into testserial (num) values(3); ОШИБКА: целое вне диапазона melkij=# select * from testserial; id | num ------------+----- 2147483646 | 1 2147483647 | 2 (2 rows) melkij=#

When the limit for int reached, postgresql refuses to insert new data. Let me remind you that serial is a 32-bit field. 64-bit - bigserial

Another interesting point:

 melkij=# select nextval('testserial_id_seq'::regclass); nextval ------------ 2147483649 (1 row) melkij=# select nextval('testserial_id_seq'::regclass); nextval ------------ 2147483650 (1 row) melkij=# alter sequence testserial_id_seq restart with 9223372036854775806;ALTER SEQUENCE melkij=# select nextval('testserial_id_seq'::regclass); nextval --------------------- 9223372036854775806 (1 row) melkij=# select nextval('testserial_id_seq'::regclass); nextval --------------------- 9223372036854775807 (1 row) melkij=# select nextval('testserial_id_seq'::regclass); ОШИБКА: функция nextval достигла максимума для последовательности "testserial_id_seq" (9223372036854775807) melkij=# alter sequence testserial_id_seq restart with 9223372036854775806 NO MAXVALUE; ALTER SEQUENCE melkij=# select nextval('testserial_id_seq'::regclass); nextval --------------------- 9223372036854775806 (1 row) melkij=# select nextval('testserial_id_seq'::regclass); nextval --------------------- 9223372036854775807 (1 row) melkij=# select nextval('testserial_id_seq'::regclass); ОШИБКА: функция nextval достигла максимума для последовательности "testserial_id_seq" (9223372036854775807) melkij=#

As you can see, the sequencing counter itself is always 64-bit. 9223372036854775807 - the limit value for him even with MAXVALUE, even without him.