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

原创于 2024-11-26 16:20:30 发布 · 876 阅读

CC 4.0 BY-SA版权

文章标签：

在 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。

注意事项

兼容性：虽然 SERIAL 类型在 PostgreSQL 中已经存在很长时间了，并且它实际上也是基于序列的自动递增列，但 IDENTITY 提供了更标准和灵活的语法。
事务回滚：如果在一个事务中插入了行但由于某种原因事务被回滚，那么这些行生成的序列值将不会被重用。这意味着序列值可能会有间隙。
性能：对于大多数应用来说，使用身份列的性能是非常好的。然而，在高并发环境下，可能需要考虑序列的性能调优。
手动插入：虽然身份列通常用于自动生成值，但你也可以手动插入值。如果尝试插入一个已经存在的值，将会导致一个唯一性约束错误。
迁移：如果你正在从旧版本的 PostgreSQL 迁移到支持身份列的新版本，你可能需要更新你的表定义以使用 IDENTITY 属性。