PostgreSQL 教程: 生成列的实践应用

摘要：在本教程中，您将了解 PostgreSQL 的生成列的实践应用。

目录

核心观点与结论

PostgreSQL 的生成列（Generated Columns）是一项强大且高效的特性。由于其计算逻辑直接下沉至数据库层，因此无需编写额外的 Python 代码或触发器（Trigger）即可保持数据的实时同步。这不仅极大提升了查询和计算性能，还能让数据模型（Models）更加干净、安全，是开发真实世界复杂应用的高效工具。

关键技术背景

• 特性引入： 生成列最早在 PostgreSQL 12（2019年）中引入。
• 存储类型：
• 存储型（Stored/Persistent）： 计算结果物理存储在数据库中（早期版本仅支持此方式）。
• 虚拟型（Virtual）： 较新版本（如 PostgreSQL 17/18）开始支持。数据在读取时实时计算，不占用存储空间，非常适合“多写少读”的场景。
• Django 支持： 在 Django 5.0 中，官方 ORM 正式引入了 GeneratedField，让开发者可以直接在 Python 代码中无缝使用该数据库特性。

Django 中的实现方法 (GeneratedField)

在 Django 模型中创建一个生成列（GeneratedField），需要指定以下三个核心参数：

1. expression（表达式）： 定义如何计算最终值的逻辑表达式。
2. output_field（输出字段类型）： 定义生成的列的数据类型。
3. db_persist（是否持久化）： 布尔值，用于指定该列是作为存储列（Persistent）还是虚拟列（Virtual）存在。

8 个典型应用场景与案例清单

最广泛使用生成列的应用场景有：

1. 基础数学计算

• 场景： 面积计算。
• 方法： 数据表中仅需存储“半径（Radius）”字段，利用生成列结合常量与函数，由数据库自动计算出“圆的面积（Area）”。

2. 价格与库存聚合

• 场景： 订单商品总价计算。
• 方法： 通过预设的“单价（Price）”和“数量（Quantity）”字段，直接相乘生成“总价（Total）”。如果仅修改数量，总价会自动在数据库底端更新。

3. 条件判断（状态自动切换）

• 场景： 订单状态更新。
• 方法： 结合 SQL 中的 Case 和 When 语句，可以判断“付款时间”是否为空。如果付款时间不为空，生成列自动将订单状态标记为“已支付（Paid）”；否则默认为“已创建（Created）”。

4. 优化 JSON 字段的查询（性能提升显著）

• 场景： 从复杂的 JSON 结构中提取特定字段（如软件包版本号）。
• 方法： 如果在查询时通过 ORM 的 Annotation 动态提取 JSON 内部键值，性能较差；而使用生成列专门将该“版本号”提取为独立字段后，实测查询速度有极大幅度（对数级）的提升，且无需读取无关的 JSON 负载。

5. 字符串拼接与数据重组

• 场景： 生成用户全名或结构化数据。
• 方法： 可以将“名”和“姓”自动拼接并加上空格，或者直接将关键字段重新组合并转化为全大写、缩写，甚至生成一个全新的 JSON 对象直接在数据库层输出。

6. 全文检索 (Full Text Search)

• 场景： 图书引言或文章搜索。
• 方法： 利用生成列自动创建 search_vector（搜索向量），系统会自动剔除停用词，实现高效的全文检索。
• 注意点： 表达式必须是“不可变（immutable）”的。如果是多语言内容，不要动态传入语言变量，而应使用 Case / When 枚举具体支持的语言来绕过此限制。

7. 日期范围筛选 (Date Range)

• 场景： 酒店预订系统。
• 方法： 将“开始日期”和“结束日期”通过生成列整合为一个“日期范围字段（Date Range Field）”，便于直接利用范围函数进行快速过滤和查询。

8. 空间与地理数据 (结合 GeoDjango/PostGIS)

• 点 (Point)： 将城市的经纬度点自动转换为 GeoHash 或标准 GeoJSON 格式存储。
• 线 (LineString)： 给定一条路线，自动计算其具体的物理长度（公里）。
• 面 (Polygon)： 给定一个州或省份的多边形边界，自动计算并生成其占地面积。

观点对比与技术总结

总结： 充分利用数据库原生能力（Lower level）是性能优化的核心原则。生成列让开发者以极低的维护成本，获得更干净的模型架构和更高的系统性能。

参考

PostgreSQL Generated Columns by Example

了解更多

PostgreSQL 教程