PostgreSQL 中的 GENERATED 列自动计算

sql CREATE TABLE 表名 ( 列1 数据类型, 列2 数据类型, 生成列名 数据类型 GENERATED ALWAYS AS (表达式) STORED );

### 示例：计算总价

```sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    quantity INT NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    total_price NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

向表内插入数据时，不能为 total_price 提供值，也不能直接更新它：

-- 正确插入，忽略生成列
INSERT INTO orders (quantity, unit_price) VALUES (3, 19.99);

-- 尝试为生成列提供值会报错
INSERT INTO orders (quantity, unit_price, total_price) VALUES (3, 19.99, 59.97);
-- ERROR:  cannot insert into column "total_price"

查询时，生成列会显示计算后的结果：

SELECT * FROM orders;

输出：

 id | quantity | unit_price | total_price
----+----------+------------+-------------
  1 |        3 |      19.99 |       59.97

更新依赖列

当你更新 quantity 或 unit_price 时，total_price 会自动重新计算并持久化存储。

UPDATE orders SET quantity = 5 WHERE id = 1;
SELECT total_price FROM orders WHERE id = 1;
-- 结果为 99.95

这保证了数据始终一致，无需手动更新或使用触发器。

使用表达式拼接字符串

生成列也可以基于字符串操作，例如合并姓和名：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

INSERT INTO users (first_name, last_name) VALUES ('Alice', 'Smith');
SELECT full_name FROM users;
-- 输出：Alice Smith

处理可能为 NULL 的列

生成列的表达式应仔细处理 NULL 值。上述拼接中，如果 first_name 或 last_name 为 NULL，连接结果也会为 NULL。可以使用 COALESCE 函数避免：

full_name TEXT GENERATED ALWAYS AS
  (COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')) STORED

生成列的表达式限制

• 必须是 IMMUTABLE 函数，不能使用如 random()、now()、nextval() 等易变函数。
• 只能引用同一张表中的列，不能跨表引用。
• 不能引用其他生成列（即生成列之间不能互相依赖）。
• 可以包含类型转换、CASE 表达式、函数（需标记为 IMMUTABLE）等。

常见不可用函数示例

-- 以下创建会失败，因为 now() 是 STABLE 而非 IMMUTABLE
CREATE TABLE test (
    created_at TIMESTAMPTZ,
    year_val INT GENERATED ALWAYS AS (EXTRACT(YEAR FROM created_at)) STORED
);
-- 实际上 EXTRACT 并不是 IMMUTABLE？需要验证，通常允许。

实际上 EXTRACT 取决于时区设置，可能不是纯粹的 IMMUTABLE，在 PostgreSQL 中定义为 IMMUTABLE 可能仍可使用，但需要小心。更安全的方式是使用 date_part 或确保上下文不会变化。

向已有表添加生成列

使用 ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS ... STORED 可以给现有表增加生成列。注意，表中已存在的行会立刻计算生成列的值，因此如果表很大，该操作可能需要较长时间并锁表。

ALTER TABLE orders
ADD COLUMN discount_price NUMERIC(10,2)
GENERATED ALWAYS AS (total_price * 0.9) STORED;

添加后，所有行的 discount_price 会自动填充为计算值。

删除生成列

删除生成列与删除普通列相同，使用 ALTER TABLE ... DROP COLUMN。

ALTER TABLE orders DROP COLUMN discount_price;

修改生成列的表达式

PostgreSQL 不支持直接修改生成列的表达式。如果需要更改，必须先删除该列，再重新添加。

-- 先删除
ALTER TABLE orders DROP COLUMN total_price;
-- 再用新的表达式重建
ALTER TABLE orders
ADD COLUMN total_price NUMERIC(10,2)
GENERATED ALWAYS AS (quantity * unit_price * 1.0) STORED; -- 假设调整计算方式

生成列与索引

生成列可以被索引，从而提高基于该派生数据的查询性能。例如，你可能经常按总价过滤订单：

CREATE INDEX idx_total_price ON orders (total_price);