Skip to content

Instantly share code, notes, and snippets.

@denishpatel

denishpatel/identity_column.md

Last active August 18, 2020 16:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save denishpatel/94642a627f0e4102ad904ffdd542e4e4 to your computer and use it in GitHub Desktop.
Save denishpatel/94642a627f0e4102ad904ffdd542e4e4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
identity_column.md

How do you create serial column?

postgres=# create table serial_test(id serial primary key,name text);
CREATE TABLE
postgres=# \d serial_test
                            Table "public.serial_test"
 Column |  Type   | Collation | Nullable |                 Default
--------+---------+-----------+----------+-----------------------------------------
 id     | integer |           | not null | nextval('serial_test_id_seq'::regclass)
 name   | text    |           |          |
Indexes:
    "serial_test_pkey" PRIMARY KEY, btree (id)

When you define serial column, it translates to following commands and then Postgres doesn't keep track of this relationship in system tables.

CREATE SEQUENCE serial_test_id_seq;

create table serial_test(id serial primary key,name text);

ALTER TABLE serial_test ALTER COLUMN id SET DEFAULT nextval('serial_test_id_seq');

ALTER SEQUENCE serial_test_id_seq OWNED BY serial_test.id;

Issues with "Serial"

  1. Non-conforming SQL Standard: SERIAL systax doesn't cionform with SQL standard which makes incompatible change bewtween databases. For exampl.e if you are working with DB2 or Oracle, they do not have SERIAL data type.
  2. Schema Management Issues:
    • Permission: Permissions for the sequence created by a serial column need to be managed separately GRANT USAGE ON SEQUENCE serial_test_id_seq;
    • ALTER: You can not manage serial with ALTER TABLE
    • dropping default does not drop sequence
    • Serial is not real data type so you can experience slight weirdness working with serial type
  3. CTAS - Create table AS
    • When you copy table with serial column using CREATE TABLE AS/ LIKE, the new table keeps using old sequence
      postgres=# create table serial_test_new (LIKE serial_Test INCLUDING ALL);
CREATE TABLE
postgres=# \d serial_test_new
                          Table "public.serial_test_new"
 Column |  Type   | Collation | Nullable |                 Default
--------+---------+-----------+----------+-----------------------------------------
 id     | integer |           | not null | nextval('serial_test_id_seq'::regclass)
 name   | text    |           |          |
Indexes:
    "serial_test_new_pkey" PRIMARY KEY, btree (id)

postgres=# \d serial_test
                            Table "public.serial_test"
 Column |  Type   | Collation | Nullable |                 Default
--------+---------+-----------+----------+-----------------------------------------
 id     | integer |           | not null | nextval('serial_test_id_seq'::regclass)
 name   | text    |           |          |
Indexes:
    "serial_test_pkey" PRIMARY KEY, btree (id)
  1. Limited max value: Serial can accomodate max value of 2147483647 (2^31-1.

bigserial

  1. Bigserial has same limitaions as "Serial" except limitation #4 from above list because bigSerial can accomodate up to 9223372036854775807 (2^63-1)

Identity column

  • Identity column was introduced in Postgres 10 for SQL conforming standard and provide robustness to manage implic sequence

Reference from documentation: https://www.postgresql.org/docs/10/sql-createtable.html

IDENTITY caluse creates the column as an identity column. It will have an implicit sequence attached to it and the column in new rows will automatically have values from the sequence assigned to it.

Two variations to command:

  1. GENERATED BY DEFAULT AS IDENTITY
  2. GENERATED ALWAYS AS IDENTITY

The clauses ALWAYS and BY DEFAULT determine how the sequence value is given precedence over a user-specified value in an INSERT statement.

If ALWAYS is specified, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is specified, then the user-specified value takes precedence. In the COPY command, user-specified values are always used regardless of this setting.

postgres=# create table identity_test(id int generated by default as identity primary key, name text);
CREATE TABLE
postgres=# \d identity_test
                        Table "public.identity_test"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
 name   | text    |           |          |
Indexes:
    "identity_test_pkey" PRIMARY KEY, btree (id)

BY DEFAULT

  • You can override system value.
postgres=# insert into identity_test(name) values ('RJ');
INSERT 0 1
postgres=# select * from identity_test;
 id | name
----+------
  1 | RJ
(1 row)

postgres=# insert into identity_test(id,name) values (3,'Nate');
INSERT 0 1
postgres=# select * from identity_test;
 id | name
----+------
  1 | RJ
  3 | Nate
(2 rows)

ALWAYS

  • You can not overwrite system value
postgres=# create table identity_always (id int generated always as identity primary key, name text);
CREATE TABLE
postgres=# insert into identity_always (name) values ('RJ');
INSERT 0 1
postgres=# insert into identity_always(id,name) values (3,'Nate');
ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
postgres=# select * from identity_always;
id | name
----+------
 1 | RJ
(1 row)

Gotchas

when you are using serial or identity by default, you can disturb the sequence but it's prevented by ALWAYS generated by system.

serial

postgres=# insert into serial_test(name) values ('RJ');
INSERT 0 1
postgres=# insert into serial_test(id,name) values (3,'Nate');
INSERT 0 1
postgres=# insert into serial_test(name) values ('Nicole');
INSERT 0 1
postgres=# insert into serial_test(name) values ('Badri');
ERROR:  duplicate key value violates unique constraint "serial_test_pkey"
DETAIL:  Key (id)=(3) already exists.

GENERATED BY DEFAULT

postgres=# insert into identity_test(name) values ('RJ');
INSERT 0 1
postgres=# select * from identity_test;
 id | name
----+------
  1 | RJ
(1 row)

postgres=# insert into identity_test(id,name) values (3,'Nate');
INSERT 0 1
postgres=# select * from identity_test;
 id | name
----+------
  1 | RJ
  3 | Nate
(2 rows)

postgres=# insert into identity_test(name) values ('Nicole');
INSERT 0 1
postgres=# select * from identity_test;
 id |  name
----+--------
  1 | RJ
  3 | Nate
  2 | Nicole
(3 rows)

postgres=# insert into identity_test(name) values ('Badri');
ERROR:  duplicate key value violates unique constraint "identity_test_pkey"
DETAIL:  Key (id)=(3) already exists.

How to convert existing Serial to bigint identify column

postgres=# create table convert_to_bigint_identity (id serial primary key, name text);
CREATE TABLE
postgres=# \d convert_to_bigint_identity
                            Table "public.convert_to_bigint_identity"
 Column |  Type   | Collation | Nullable |                        Default
--------+---------+-----------+----------+--------------------------------------------------------
 id     | integer |           | not null | nextval('convert_to_bigint_identity_id_seq'::regclass)
 name   | text    |           |          |
Indexes:
    "convert_to_bigint_identity_pkey" PRIMARY KEY, btree (id)

postgres=# insert into convert_to_bigint_identity(name) values ('a');
INSERT 0 1
postgres=# insert into convert_to_bigint_identity(name) values ('b');
INSERT 0 1
postgres=# insert into convert_to_bigint_identity(name) values ('c');
INSERT 0 1
postgres=# insert into convert_to_bigint_identity(name) values ('d');
INSERT 0 1
postgres=# select * from convert_to_bigint_identity;
 id | name
----+------
  1 | a
  2 | b
  3 | c
  4 | d
(4 rows)

postgres=# alter table convert_to_bigint_identity alter id drop default, alter id type bigint , alter id add generated always as identity (start with 5);
ALTER TABLE
postgres=# \d convert_to_bigint_identity
               Table "public.convert_to_bigint_identity"
 Column |  Type  | Collation | Nullable |           Default
--------+--------+-----------+----------+------------------------------
 id     | bigint |           | not null | generated always as identity
 name   | text   |           |          |
Indexes:
    "convert_to_bigint_identity_pkey" PRIMARY KEY, btree (id)

postgres=# drop sequence convert_to_bigint_identity_id_seq;
DROP SEQUENCE

postgres=# insert into convert_to_bigint_identity(name) values ('e');
INSERT 0 1
postgres=# select * from convert_to_bigint_identity;
 id | name
----+------
  1 | a
  2 | b
  3 | c
  4 | d
  5 | e
(5 rows)

Conclusion

IDENTIY column with ALWAYS system generated values gives robust solution and it adther to SQL standards. if we are concerned about max value of INT type, we should consider migrating INT type to BIGINT.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment