PostgreSQL 中Identity Columns生成一个唯一的标识符

在 PostgreSQL 中，从版本 10 开始引入了“身份列”（Identity Columns）的概念，这是一种简化和标准化主键生成的方式，类似于其他数据库系统（如 SQL Server）中的 IDENTITY 属性。身份列允许数据库自动为表中的每一行生成一个唯一的标识符，通常用于主键字段。

使用身份列

要在 PostgreSQL 中创建一个带有身份列的表，你可以使用 GENERATED ALWAYS AS IDENTITY 子句。这里有一个简单的例子：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- SERIAL 是一种简化的方式，等同于使用 IDENTITY
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

或者，更明确地使用 IDENTITY 关键字：

To create an identity column, use the GENERATED ALWAYS AS IDENTITY clause in CREATE TABLE

CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

To create an identity column, use the GENERATED BY DEFAULT AS IDENTITY clause in CREATE TABLE

CREATE TABLE users (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

在这个例子中，id 列被设置为身份列，这意味着每当向 users 表中插入一行新数据时，id 列的值会自动递增。

superdb=# insert into users(username,email) values('A001','SZ');
INSERT 0 1
superdb=# insert into users(username,email) values('A002','GZ');
INSERT 0 1
superdb=# select * from users;
 id | username | email
----+----------+-------
  1 | A001     | SZ
  2 | A002     | GZ
(2 rows)

-- 指定关键字DEFAULT代替值以显式请求序列生成的值

superdb=# insert into users(id,username,email) values(DEFAULT,'A003','BJ');
INSERT 0 1
superdb=# select * from users;
 id | username | email
----+----------+-------
  1 | A001     | SZ
  2 | A002     | GZ
  3 | A003     | BJ
(3 rows)

自定义序列

默认情况下，身份列使用默认的序列生成器，但你也可以指定一个自定义的序列：

CREATE SEQUENCE custom_seq START WITH 1000 INCREMENT BY 1;

CREATE TABLE users (
    id INT GENERATED ALWAYS AS IDENTITY (SEQUENCE = custom_seq) PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

在这个例子中，id 列将使用 custom_seq 序列来生成值，从 1000 开始，每次递增 1。

注意事项

1. 兼容性：虽然 SERIAL 类型在 PostgreSQL 中已经存在很长时间了，并且它实际上也是基于序列的自动递增列，但 IDENTITY 提供了更标准和灵活的语法。

2. 事务回滚：如果在一个事务中插入了行但由于某种原因事务被回滚，那么这些行生成的序列值将不会被重用。这意味着序列值可能会有间隙。

3. 性能：对于大多数应用来说，使用身份列的性能是非常好的。然而，在高并发环境下，可能需要考虑序列的性能调优。

4. 手动插入：虽然身份列通常用于自动生成值，但你也可以手动插入值。如果尝试插入一个已经存在的值，将会导致一个唯一性约束错误。

5. 迁移：如果你正在从旧版本的 PostgreSQL 迁移到支持身份列的新版本，你可能需要更新你的表定义以使用 IDENTITY 属性。

通过使用身份列，PostgreSQL 提供了一种简洁而强大的方式来管理主键和其他需要唯一标识符的列。