生成列

生成列（Generated Columns）是 PostgreSQL 中一种特殊的列类型，它的值总是由其他列计算而来。本章将详细介绍生成列的概念、语法、使用场景和最佳实践。

什么是生成列

生成列是一种特殊的列，它总是由其他列计算而来。因此，对于列来说，它就像视图对于表一样。生成列的值不能直接写入，而是根据定义的表达式自动计算。

生成列特点

值始终由其他列计算而来
不能直接写入数据
提供两种类型：存储型和虚拟型
当前 PostgreSQL 仅支持存储型

生成列类型

存储型生成列（STORED）

计算时机：在写入（INSERT 或 UPDATE）时计算
存储方式：像普通列一样占用存储空间
特点：类似于自动更新的物化视图

虚拟型生成列（VIRTUAL）

计算时机：在读取时计算
存储方式：不占用存储空间
特点：类似于视图
支持状态：PostgreSQL 目前尚未实现

基本语法

创建生成列

sql

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    generated_column data_type GENERATED ALWAYS AS (expression) STORED
);

语法要点

必须使用 GENERATED ALWAYS AS 关键字
表达式放在括号中
必须指定 STORED 关键字
生成列的数据类型必须与表达式结果兼容

详细示例

示例 1：基本生成列使用

问题陈述：创建一个人员表，包含身高的厘米和英寸两种单位，英寸值自动从厘米值计算。

解决方案：

sql

-- 创建包含生成列的表
CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    height_cm NUMERIC(5,2),
    height_in NUMERIC(5,2) GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

测试数据操作：

sql

-- 插入数据（不需要指定生成列的值）
INSERT INTO people (name, height_cm) VALUES 
    ('张三', 175.5),
    ('李四', 168.0),
    ('王五', 182.3);

-- 查询结果
SELECT * FROM people;

输出结果：

id	name	height_cm	height_in
1	张三	175.50	69.09
2	李四	168.00	66.14
3	王五	182.30	71.77

分析过程：

height_in 列自动根据公式 height_cm / 2.54 计算
插入时只需提供 height_cm 值
生成列的值会自动存储在磁盘上

示例 2：复杂表达式生成列

问题陈述：创建订单表，包含单价、数量和总金额，总金额自动计算，并包含税费计算。

解决方案：

sql

-- 创建订单表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_name VARCHAR(200),
    unit_price DECIMAL(10,2),
    quantity INTEGER,
    subtotal DECIMAL(12,2) GENERATED ALWAYS AS (unit_price * quantity) STORED,
    tax_rate DECIMAL(5,4) DEFAULT 0.0825, -- 8.25% 税率
    total_amount DECIMAL(12,2) GENERATED ALWAYS AS (
        unit_price * quantity * (1 + tax_rate)
    ) STORED
);

测试数据：

sql

-- 插入测试数据
INSERT INTO orders (product_name, unit_price, quantity) VALUES 
    ('笔记本电脑', 8999.99, 2),
    ('无线鼠标', 299.50, 5),
    ('机械键盘', 599.00, 3);

-- 查询结果
SELECT 
    order_id,
    product_name,
    unit_price,
    quantity,
    subtotal,
    tax_rate,
    total_amount
FROM orders;

输出结果：

order_id	product_name	unit_price	quantity	subtotal	tax_rate	total_amount
1	笔记本电脑	8999.99	2	17999.98	0.0825	19484.98
2	无线鼠标	299.50	5	1497.50	0.0825	1621.11
3	机械键盘	599.00	3	1797.00	0.0825	1945.40

示例 3：字符串操作生成列

问题陈述：创建用户表，自动生成全名和邮箱地址。

解决方案：

sql

-- 创建用户表
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    username VARCHAR(50) NOT NULL,
    domain VARCHAR(50) DEFAULT 'company.com',
    full_name VARCHAR(101) GENERATED ALWAYS AS (
        first_name || ' ' || last_name
    ) STORED,
    email VARCHAR(150) GENERATED ALWAYS AS (
        lower(username || '@' || domain)
    ) STORED
);

测试数据：

sql

-- 插入数据
INSERT INTO users (first_name, last_name, username) VALUES 
    ('张', '三', 'zhangsan'),
    ('李', '四', 'lisi'),
    ('王', '五', 'wangwu');

-- 查询结果
SELECT user_id, first_name, last_name, full_name, email FROM users;

输出结果：

user_id	first_name	last_name	full_name	email
1	张	三	张三	[email protected]
2	李	四	李四	[email protected]
3	王	五	王五	[email protected]

生成列的操作

插入数据

sql

-- 正确的插入方式（不指定生成列）
INSERT INTO people (name, height_cm) VALUES ('新用户', 170.0);

-- 使用 DEFAULT 关键字（可选）
INSERT INTO people (name, height_cm, height_in) VALUES ('新用户2', 165.0, DEFAULT);

注意事项

sql

-- 错误：不能直接为生成列赋值
INSERT INTO people (name, height_cm, height_in) VALUES ('错误示例', 170.0, 67.0);
-- 这将产生错误：ERROR: cannot insert into column "height_in"

更新数据

sql

-- 更新基础列，生成列会自动重新计算
UPDATE people SET height_cm = 180.0 WHERE id = 1;

-- 查看更新后的结果
SELECT id, name, height_cm, height_in FROM people WHERE id = 1;

输出结果：

id	name	height_cm	height_in
1	张三	180.00	70.87

生成列的限制

表达式限制

只能使用不可变函数
不能使用子查询
不能引用其他生成列
不能引用系统列
不能引用当前行外内容

示例：限制演示

sql

-- 正确：使用不可变函数
CREATE TABLE test_constraints (
    id SERIAL,
    value1 NUMERIC,
    value2 NUMERIC,
    sum_value NUMERIC GENERATED ALWAYS AS (value1 + value2) STORED,
    abs_value NUMERIC GENERATED ALWAYS AS (ABS(value1)) STORED
);

-- 错误示例：

禁止的操作

sql

-- 错误：使用易变函数
CREATE TABLE bad_example1 (
    id SERIAL,
    created_at TIMESTAMP GENERATED ALWAYS AS (NOW()) STORED  -- 错误
);

-- 错误：引用其他生成列
CREATE TABLE bad_example2 (
    val1 NUMERIC,
    gen1 NUMERIC GENERATED ALWAYS AS (val1 * 2) STORED,
    gen2 NUMERIC GENERATED ALWAYS AS (gen1 * 3) STORED  -- 错误
);

-- 错误：使用子查询
CREATE TABLE bad_example3 (
    id SERIAL,
    category_id INTEGER,
    category_name TEXT GENERATED ALWAYS AS (
        (SELECT name FROM categories WHERE id = category_id)  -- 错误
    ) STORED
);

继承和分区中的生成列

继承表中的生成列

sql

-- 父表
CREATE TABLE vehicles (
    id SERIAL PRIMARY KEY,
    brand VARCHAR(50),
    model VARCHAR(50),
    full_name VARCHAR(101) GENERATED ALWAYS AS (brand || ' ' || model) STORED
);

-- 子表：继承父表的生成列定义
CREATE TABLE cars (
    doors INTEGER,
    engine_type VARCHAR(20)
) INHERITS (vehicles);

-- 子表：覆盖生成列表达式
CREATE TABLE motorcycles (
    cc INTEGER,
    full_name VARCHAR(101) GENERATED ALWAYS AS (
        brand || ' ' || model || ' (' || cc || 'cc)'
    ) STORED
) INHERITS (vehicles);

分区表中的生成列

sql

-- 创建分区表
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE,
    amount DECIMAL(10,2),
    tax DECIMAL(10,2) GENERATED ALWAYS AS (amount * 0.1) STORED,
    year INTEGER GENERATED ALWAYS AS (EXTRACT(YEAR FROM sale_date)) STORED
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

生成列的高级应用

示例 4：JSON 数据提取

问题陈述：存储 JSON 格式的用户配置，并自动提取常用字段便于查询。

解决方案：

sql

-- 创建用户配置表
CREATE TABLE user_preferences (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    preferences JSONB,
    theme VARCHAR(20) GENERATED ALWAYS AS (preferences->>'theme') STORED,
    language VARCHAR(10) GENERATED ALWAYS AS (preferences->>'language') STORED,
    notifications_enabled BOOLEAN GENERATED ALWAYS AS (
        (preferences->>'notifications')::BOOLEAN
    ) STORED
);

测试数据：

sql

-- 插入 JSON 配置数据
INSERT INTO user_preferences (username, preferences) VALUES 
    ('user1', '{"theme": "dark", "language": "zh", "notifications": true}'),
    ('user2', '{"theme": "light", "language": "en", "notifications": false}'),
    ('user3', '{"theme": "auto", "language": "zh", "notifications": true}');

-- 查询提取的字段
SELECT username, theme, language, notifications_enabled FROM user_preferences;

输出结果：

username	theme	language	notifications_enabled
user1	dark	zh	t
user2	light	en	f
user3	auto	zh	t

示例 5：时间计算生成列

问题陈述：员工表需要根据生日自动计算年龄和工龄。

解决方案：

sql

-- 创建员工表
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    birth_date DATE,
    hire_date DATE,
    age INTEGER GENERATED ALWAYS AS (
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date))
    ) STORED,
    years_of_service NUMERIC(4,1) GENERATED ALWAYS AS (
        ROUND(EXTRACT(EPOCH FROM AGE(CURRENT_DATE, hire_date)) / (365.25 * 24 * 3600), 1)
    ) STORED
);

注意

上述示例中使用了 CURRENT_DATE，这在实际应用中可能不适合生成列，因为它是易变的。在实际应用中，应该考虑使用触发器或在查询时计算。

正确的年龄计算方案：

sql

-- 修正版本：创建视图来处理动态计算
CREATE TABLE employees_base (
    emp_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    birth_date DATE,
    hire_date DATE
);

-- 创建视图计算年龄
CREATE VIEW employees AS
SELECT 
    emp_id,
    name,
    birth_date,
    hire_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age,
    ROUND(EXTRACT(EPOCH FROM AGE(CURRENT_DATE, hire_date)) / (365.25 * 24 * 3600), 1) AS years_of_service
FROM employees_base;

最佳实践

1. 适用场景

推荐使用场景

数据转换：单位转换、格式转换
计算字段：价格计算、统计值
数据标准化：全名组合、邮箱生成
索引优化：复杂表达式的预计算

2. 设计原则

3. 代码示例：最佳实践

sql

-- 良好的生成列设计示例
CREATE TABLE product_catalog (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    category VARCHAR(50),
    price DECIMAL(10,2),
    discount_rate DECIMAL(4,3) DEFAULT 0.000,
    
    -- 简单、有用的生成列
    sale_price DECIMAL(10,2) GENERATED ALWAYS AS (
        price * (1 - discount_rate)
    ) STORED,
    
    -- 便于搜索的标准化字段
    search_name TEXT GENERATED ALWAYS AS (
        lower(regexp_replace(name, '[^a-zA-Z0-9\s]', '', 'g'))
    ) STORED,
    
    -- 分类标签
    price_category VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN price < 100 THEN 'budget'
            WHEN price < 500 THEN 'mid-range'
            ELSE 'premium'
        END
    ) STORED
);

-- 在生成列上创建有用的索引
CREATE INDEX idx_product_sale_price ON product_catalog(sale_price);
CREATE INDEX idx_product_price_category ON product_catalog(price_category);
CREATE INDEX idx_product_search_name ON product_catalog USING gin(to_tsvector('simple', search_name));

生成列与其他特性的对比

生成列 vs 默认值

特性	生成列	默认值
计算时机	每次写入时重新计算	仅在插入时计算一次
依赖关系	可以引用其他列	不能引用其他列
更新行为	自动更新	不会自动更新
函数限制	仅不可变函数	可以使用易变函数

生成列 vs 触发器

特性	生成列	触发器
性能	较高（内置优化）	较低（额外开销）
复杂度	简单表达式	复杂逻辑
维护性	自动维护	需要手动维护
灵活性	有限	高度灵活

生成列 vs 视图

特性	生成列	视图
存储	占用存储空间	不占用存储空间
查询性能	高（预计算）	低（实时计算）
索引支持	支持	有限支持
更新开销	写入时计算	无更新开销

故障排除

常见错误及解决方案

1. 语法错误

sql

-- 错误：缺少 STORED 关键字
CREATE TABLE wrong_syntax (
    id SERIAL,
    value1 INTEGER,
    value2 INTEGER GENERATED ALWAYS AS (value1 * 2)  -- 错误
);

-- 正确写法
CREATE TABLE correct_syntax (
    id SERIAL,
    value1 INTEGER,
    value2 INTEGER GENERATED ALWAYS AS (value1 * 2) STORED
);

2. 类型不匹配

sql

-- 错误：类型不匹配
CREATE TABLE type_mismatch (
    id SERIAL,
    name VARCHAR(50),
    length INTEGER GENERATED ALWAYS AS (length(name)) STORED  -- length() 返回 INTEGER，匹配
);

-- 注意：确保表达式返回类型与列类型兼容

3. 循环依赖

sql

-- 错误：循环依赖
CREATE TABLE circular_dependency (
    id SERIAL,
    a INTEGER GENERATED ALWAYS AS (b + 1) STORED,    -- 错误：引用另一个生成列
    b INTEGER GENERATED ALWAYS AS (a + 1) STORED     -- 错误：循环依赖
);

实际应用场景

电商系统价格计算

sql

-- 电商商品表
CREATE TABLE ecommerce_products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    base_price DECIMAL(10,2) NOT NULL,
    discount_percentage DECIMAL(5,2) DEFAULT 0.00,
    tax_rate DECIMAL(5,4) DEFAULT 0.0875, -- 8.75% 税率
    
    -- 自动计算字段
    discount_amount DECIMAL(10,2) GENERATED ALWAYS AS (
        base_price * discount_percentage / 100
    ) STORED,
    
    discounted_price DECIMAL(10,2) GENERATED ALWAYS AS (
        base_price - (base_price * discount_percentage / 100)
    ) STORED,
    
    final_price DECIMAL(10,2) GENERATED ALWAYS AS (
        (base_price - (base_price * discount_percentage / 100)) * (1 + tax_rate)
    ) STORED,
    
    price_tier VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN base_price < 50 THEN 'low'
            WHEN base_price < 200 THEN 'medium'
            WHEN base_price < 500 THEN 'high'
            ELSE 'premium'
        END
    ) STORED
);

-- 插入示例数据
INSERT INTO ecommerce_products (name, base_price, discount_percentage) VALUES
    ('无线耳机', 199.99, 15.00),
    ('智能手环', 299.50, 10.00),
    ('机械键盘', 599.00, 5.00);

-- 查看计算结果
SELECT 
    name,
    base_price,
    discount_percentage,
    discount_amount,
    discounted_price,
    final_price,
    price_tier
FROM ecommerce_products;

总结

生成列是 PostgreSQL 中一个强大的特性，它提供了以下优势：

主要优势

自动维护：值始终与依赖列保持同步
性能优化：预计算复杂表达式，支持索引
数据一致性：避免手动维护计算字段的错误
简化查询：复杂计算逻辑封装在表结构中

使用建议

适用于稳定的计算逻辑
优先考虑简单的数学和字符串操作
合理评估存储空间成本
在生成列上创建索引以提高查询性能
避免在生成列中使用易变函数

通过合理使用生成列，可以显著简化应用程序逻辑，提高数据一致性和查询性能。在设计时应该权衡计算复杂度、存储成本和查询性能等因素，选择最适合的实现方案。

生成列 ​

什么是生成列 ​

生成列类型 ​

存储型生成列（STORED） ​

虚拟型生成列（VIRTUAL） ​

基本语法 ​

创建生成列 ​

详细示例 ​

示例 1：基本生成列使用 ​

示例 2：复杂表达式生成列 ​

示例 3：字符串操作生成列 ​

生成列的操作 ​

插入数据 ​

更新数据 ​

生成列的限制 ​

表达式限制 ​

示例：限制演示 ​

继承和分区中的生成列 ​

继承表中的生成列 ​

分区表中的生成列 ​

生成列的高级应用 ​

示例 4：JSON 数据提取 ​

示例 5：时间计算生成列 ​

最佳实践 ​

1. 适用场景 ​

2. 设计原则 ​

3. 代码示例：最佳实践 ​

生成列与其他特性的对比 ​

生成列 vs 默认值 ​

生成列 vs 触发器 ​

生成列 vs 视图 ​

故障排除 ​

常见错误及解决方案 ​

1. 语法错误 ​

2. 类型不匹配 ​

3. 循环依赖 ​

实际应用场景 ​

电商系统价格计算 ​

总结 ​

生成列

什么是生成列

生成列类型

存储型生成列（STORED）

虚拟型生成列（VIRTUAL）

基本语法

创建生成列

详细示例

示例 1：基本生成列使用

示例 2：复杂表达式生成列

示例 3：字符串操作生成列

生成列的操作

插入数据

更新数据

生成列的限制

表达式限制

示例：限制演示

继承和分区中的生成列

继承表中的生成列

分区表中的生成列

生成列的高级应用

示例 4：JSON 数据提取

示例 5：时间计算生成列

最佳实践

1. 适用场景

2. 设计原则

3. 代码示例：最佳实践

生成列与其他特性的对比

生成列 vs 默认值

生成列 vs 触发器

生成列 vs 视图

故障排除

常见错误及解决方案

1. 语法错误

2. 类型不匹配

3. 循环依赖

实际应用场景

电商系统价格计算

总结