Skip to content

标识列 (Identity Columns)

标识列是 PostgreSQL 中一种特殊的列类型,它能够从隐式序列中自动生成唯一值。这个特性在创建主键或需要自增序号的场景中非常有用。

什么是标识列?

标识列是一个能够自动生成数值的特殊列,它基于内部序列机制工作,无需手动指定值就能为每一行生成唯一的标识符。

📖 基本概念

标识列的核心特点:

  • 自动生成:无需手动插入值,系统会自动分配
  • 基于序列:底层使用 PostgreSQL 的序列(SEQUENCE)机制
  • 类型限制:只能使用序列支持的数据类型
  • NOT NULL:自动标记为非空约束

🛠️ 创建标识列

语法形式

PostgreSQL 提供两种创建标识列的方式:

sql
-- 方式1: GENERATED ALWAYS AS IDENTITY
CREATE TABLE table_name (
    column_name data_type GENERATED ALWAYS AS IDENTITY,
    ...
);

-- 方式2: GENERATED BY DEFAULT AS IDENTITY  
CREATE TABLE table_name (
    column_name data_type GENERATED BY DEFAULT AS IDENTITY,
    ...
);

ALWAYS vs BY DEFAULT 对比

特性GENERATED ALWAYSGENERATED BY DEFAULT
用户指定值默认拒绝允许覆盖
保护级别高(防止意外插入)中(类似默认值)
覆盖方式需要 OVERRIDING SYSTEM VALUE直接指定值即可
适用场景严格控制的主键灵活的自增字段

💡 实际应用示例

示例1:创建用户表

让我们创建一个用户表来演示标识列的使用:

sql
-- 创建带有标识列的用户表
CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY,
    username varchar(50) NOT NULL,
    email varchar(100) NOT NULL,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

分析过程:

  • id 列使用 GENERATED ALWAYS AS IDENTITY,确保每个用户都有唯一的标识符
  • 系统会自动为每一行生成递增的 ID 值
  • 用户无法直接插入 ID 值(除非使用特殊语法)

示例2:插入数据操作

sql
-- 基本插入(推荐方式)
INSERT INTO users (username, email) 
VALUES ('alice', 'alice@example.com');

INSERT INTO users (username, email) 
VALUES ('bob', 'bob@example.com');

-- 使用 DEFAULT 关键字
INSERT INTO users (id, username, email) 
VALUES (DEFAULT, 'charlie', 'charlie@example.com');

输入和输出:

查询结果:

sql
SELECT * FROM users;
idusernameemailcreated_at
1alicealice@example.com2024-01-15 10:30:00
2bobbob@example.com2024-01-15 10:31:00
3charliecharlie@example.com2024-01-15 10:32:00

示例3:BY DEFAULT 类型的灵活性

sql
-- 创建更灵活的产品表
CREATE TABLE products (
    id bigint GENERATED BY DEFAULT AS IDENTITY,
    name varchar(100) NOT NULL,
    price decimal(10,2)
);

-- 可以直接指定 ID 值
INSERT INTO products (id, name, price) VALUES (100, 'Special Product', 99.99);
INSERT INTO products (name, price) VALUES ('Regular Product', 29.99);

输出结果:

idnameprice
100Special Product99.99
1Regular Product29.99

🔄 标识列操作流程

Syntax error in textmermaid version 11.8.0

⚙️ 高级操作

覆盖 ALWAYS 类型的限制

sql
-- 对于 GENERATED ALWAYS 类型,强制插入指定值
INSERT INTO users (id, username, email) 
OVERRIDING SYSTEM VALUE 
VALUES (1000, 'admin', 'admin@example.com');

在 UPDATE 中使用

sql
-- 重置标识列值为下一个序列值
UPDATE users 
SET id = DEFAULT 
WHERE username = 'alice';

修改标识列属性

sql
-- 修改序列的起始值和增量
ALTER TABLE users 
ALTER COLUMN id SET GENERATED BY DEFAULT;

-- 重启序列值
ALTER TABLE users 
ALTER COLUMN id RESTART WITH 2000;

📊 数据类型支持

标识列支持的数据类型与序列相同:

数据类型范围适用场景
smallint-32,768 到 32,767小型表
integer-2,147,483,648 到 2,147,483,647一般应用
bigint-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807大型应用

⚠️ 重要注意事项

唯一性问题

标识列不保证唯一性!如果需要唯一性,必须添加 PRIMARY KEY 或 UNIQUE 约束。

sql
CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- 添加主键约束
    username varchar(50) UNIQUE,  -- 用户名唯一
    email varchar(100) NOT NULL
);

性能考虑

  • 标识列使用序列,在高并发插入时性能优异
  • 避免在标识列上使用 OVERRIDING SYSTEM VALUE,这可能导致序列值混乱
  • 考虑使用 bigint 类型避免数值溢出

🏗️ 表继承中的标识列

父子表关系

sql
-- 父表
CREATE TABLE animals (
    id bigint GENERATED ALWAYS AS IDENTITY,
    name varchar(50)
);

-- 子表(不会继承标识列属性)
CREATE TABLE dogs (
    breed varchar(30)
) INHERITS (animals);

重要特点:

  • 子表不会自动继承父表的标识列属性
  • 需要单独为子表定义标识列
  • 每个表的标识列独立工作

分区表中的标识列

sql
-- 分区主表
CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY,
    order_date date,
    amount decimal(10,2)
) PARTITION BY RANGE (order_date);

-- 分区会继承标识列
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

🔍 标识列管理查询

查看标识列信息

sql
-- 查询表的标识列信息
SELECT 
    table_name,
    column_name,
    is_identity,
    identity_generation,
    identity_start,
    identity_increment
FROM information_schema.columns 
WHERE table_name = 'users' 
  AND is_identity = 'YES';

获取当前序列值

sql
-- 查看标识列关联的序列
SELECT pg_get_serial_sequence('users', 'id');

-- 获取当前序列值
SELECT currval(pg_get_serial_sequence('users', 'id'));

📝 最佳实践

建议

  1. 选择合适的类型:一般情况下推荐使用 GENERATED ALWAYS
  2. 添加约束:始终为标识列添加 PRIMARY KEY 约束
  3. 合理命名:使用有意义的列名,如 user_idorder_id
  4. 预留空间:对于大型应用,考虑使用 bigint 类型
  5. 监控使用:定期检查序列值的使用情况,避免溢出
标识列 vs 序列 vs UUID

标识列的优势:

  • 语法简洁,易于理解
  • 自动管理序列生命周期
  • 更好的标准 SQL 兼容性

适用场景:

  • 需要连续递增数字的场景
  • 单机数据库环境
  • 对性能有较高要求的应用

替代方案:

  • 手动序列:更灵活的控制
  • UUID:分布式环境下的全局唯一性

🎯 总结

标识列是 PostgreSQL 中创建自增主键的现代化方式,它提供了:

  • 简洁的语法:相比传统序列更易使用
  • 自动管理:无需手动维护序列对象
  • 灵活控制:通过 ALWAYS/BY DEFAULT 控制插入行为
  • 标准兼容:符合 SQL 标准的 IDENTITY 规范

掌握标识列的使用对于设计高效的 PostgreSQL 数据库架构至关重要。在实际项目中,建议优先考虑使用标识列替代传统的序列+默认值组合。