Appearance
标识列 (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 ALWAYS | GENERATED 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;
id | username | created_at | |
---|---|---|---|
1 | alice | alice@example.com | 2024-01-15 10:30:00 |
2 | bob | bob@example.com | 2024-01-15 10:31:00 |
3 | charlie | charlie@example.com | 2024-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);
输出结果:
id | name | price |
---|---|---|
100 | Special Product | 99.99 |
1 | Regular Product | 29.99 |
🔄 标识列操作流程
⚙️ 高级操作
覆盖 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'));
📝 最佳实践
建议
- 选择合适的类型:一般情况下推荐使用
GENERATED ALWAYS
- 添加约束:始终为标识列添加 PRIMARY KEY 约束
- 合理命名:使用有意义的列名,如
user_id
、order_id
- 预留空间:对于大型应用,考虑使用
bigint
类型 - 监控使用:定期检查序列值的使用情况,避免溢出
标识列 vs 序列 vs UUID
标识列的优势:
- 语法简洁,易于理解
- 自动管理序列生命周期
- 更好的标准 SQL 兼容性
适用场景:
- 需要连续递增数字的场景
- 单机数据库环境
- 对性能有较高要求的应用
替代方案:
- 手动序列:更灵活的控制
- UUID:分布式环境下的全局唯一性
🎯 总结
标识列是 PostgreSQL 中创建自增主键的现代化方式,它提供了:
- 简洁的语法:相比传统序列更易使用
- 自动管理:无需手动维护序列对象
- 灵活控制:通过 ALWAYS/BY DEFAULT 控制插入行为
- 标准兼容:符合 SQL 标准的 IDENTITY 规范
掌握标识列的使用对于设计高效的 PostgreSQL 数据库架构至关重要。在实际项目中,建议优先考虑使用标识列替代传统的序列+默认值组合。