官术网_书友最值得收藏!

Serial types and identity columns

Serial types and identity columns are used to define surrogate keys. The synopsis for defining a column as an identity column is as follows:

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

It's better to use identity columns, which were introduced in PostgreSQL 10, rather than serial, because this overcomes some of the serial type's limitations:

  • Compatibility: The identity column adheres to SQL standards, this makes it easier to migrate PostgreSQL to other relational databases and vice versa. 
  • Permissions: The permissions of the sequence object that was created by using the serial column is managed separately. Often, developers tend to forget to assign proper permissions to the sequence object when changing the permissions of the table that contains the defined serial type.
  • Sequence value and user data precedence: The serial type uses default constrain to assign the value of the column. That means you can override the default values. The identity column can control this behavior. The BY DEFAULT option allows the user to insert data into the column. If ALWAYS is specified, the user value won't be accepted unless the INSERT statement specifies OVERRIDING SYSTEM VALUE. Note that this setting is ignored by the COPY statement. 
  • Managing table structure: It's easier, from a syntactical point view, to manage the identity column. It's also easier to alter existing columns' default values if they're defined as an identity. 

Both identity columns and serial types use sequence objects behind the scenes. In most cases, it's straightforward to replace a serial type with an identity column, because both behave similarly. 

The following examples shows how to define surrogate keys using an identity column and a serial type. They also show the limitations of the serial type:

  • Create a table of the SERIAL type and perform an INSERT operation:
postgres=# CREATE TABLE test_serial (id SERIAL PRIMARY KEY, payload text);
CREATE TABLE
postgres=# INSERT INTO test_serial (payload) SELECT 'a' RETURNING *;
id | payload
----+---------
1 | a
(1 row)
postgres=# INSERT INTO test_serial (id, payload) SELECT 2, 'a' RETURNING *;
id | payload
----+---------
2 | a
(1 row)
postgres=# INSERT INTO test_serial (payload) SELECT 'a' RETURNING *;
ERROR: duplicate key value violates unique constraint "test_serial_pkey"
DETAIL: Key (id)=(2) already exists.
  • Create a table with IDENTITY and perform an INSERT operation:
postgres=# CREATE TABLE test_identity ( id INTEGER generated by default as identity PRIMARY KEY, payload text);
CREATE TABLE
postgres=# INSERT INTO test_identity (payload) SELECT 'a' RETURNING *;
id | payload
----+---------
1 | a
(1 row)
postgres=# INSERT INTO test_identity (id, payload) SELECT 1, 'a' RETURNING *;
ERROR: duplicate key value violates unique constraint "test_identity_pkey"
DETAIL: Key (id)=(1) already exists.
postgres=# INSERT INTO test_identity (id, payload) SELECT 100000, 'a' RETURNING *;
id | payload
--------+---------
100000 | a
(1 row)

  • IDENTITY and SERIAL internal implementation depends on sequence:
postgres=# \ds 
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+----------
public | test_identity_id_seq | sequence | postgres
public | test_serial_id_seq | sequence | postgres
(2 rows)
  • Create an IDENTITY column with the ALWAYS option:
postgres=# CREATE TABLE test_identity2 ( id INTEGER generated always as identity PRIMARY KEY, payload text);
CREATE TABLE
postgres=# INSERT INTO test_identity2 (id, payload) SELECT 1, 'a' RETURNING *;
ERROR: cannot insert into column "id"
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.

IDENTITY columns are developed to overcome known limitations of the SERIAL type; mainly, permission issues and comparability.

主站蜘蛛池模板: 广州市| 东宁县| 曲周县| 蒙山县| 宁南县| 丹江口市| 广德县| 台东市| 柳江县| 宿迁市| 玛多县| 富阳市| 博客| 信宜市| 台湾省| 邯郸县| 奈曼旗| 武清区| 丽水市| 株洲县| 三穗县| 闻喜县| 高密市| 屯昌县| 延安市| 沁阳市| 正安县| 曲阳县| 肥城市| 广州市| 安阳市| 夏津县| 疏附县| 呈贡县| 嘉禾县| 安龙县| 泰宁县| 车致| 喀喇| 石棉县| 武夷山市|