From: | Pantelis Theodosiou <ypercube(at)gmail(dot)com> |
---|---|
To: | pgsql-docs(at)postgresql(dot)org |
Subject: | Changes in serial / sequence introduced in Postgresql 10 |
Date: | 2017-10-06 13:08:24 |
Message-ID: | CAE3TBxz4BkhcvYwCxtRpQyqu5NVjMAxgun5vhasM5C+kDcuCRA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
I noticed that for a column is defined as serial, there are differences in
the created sequence (type and maximum value) in Postgres 10.
In 9.6, the sequence create would have a maximum value of 2**64-1. In 10,
it's created with 2**32-1 and I couldn't find this change in the release
notes or in the docs.
-- Postgres 9.6 --
x=# select version() ;
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)
x=# create table test (id serial primary key) ;
CREATE TABLE
x=# \d test_id_seq
Sequence "public.test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? |
Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no |
1
Owned by: public.test.id
-- Postgres 10 --
x=# select version() ;
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)
x=# create table test (id serial primary key) ;
CREATE TABLE
x=# \d test_id_seq
Sequence "public.test_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.test.id
I suppose it's not a very common use case but I noticed because I had some
tables that were created with serial columns, then later converted to
bigint with:
alter table test alter column id type bigint using id::bigint ;
without need to modify the sequence.
In 10, the same operation would modify only the column that later cause an
error when the maximum value is reached.
The change in behaviour is I guess due to the identity columns feature and
I think it would be good to be somewhere in the documentation or the
release notes - assuming that it was intentional.
Pantelis Theodosiou
From | Date | Subject | |
---|---|---|---|
Next Message | diemersebastien | 2017-10-09 09:07:48 | Correction in SHARE ROW EXCLUSIVE lock description |
Previous Message | redirect.null | 2017-10-06 10:06:49 | 19.2. File Locations doesn't tell you the file locations! |