PostgreSQL 教程: 生成列

六月 4, 2024

摘要:在本教程中,您将了解 PostgreSQL 的生成列,其值是自动从其他列计算出来的。

目录

PostgreSQL 生成列简介

在 PostgreSQL 中,生成列是一种特殊类型的列,其值是根据表达式或其他列的值自动计算出来的。

生成列在 SQL Server 中称为计算列,在 Oracle 中称为虚拟列。

有两种类型的生成列:

  • 存储型:存储型生成列在插入或更新时计算,并占用存储空间。
  • 虚拟型:虚拟型生成列在读取时计算,不占用存储空间。

虚拟型生成列类似于视图,而存储型生成列类似于物化视图。与物化视图不同,PostgreSQL 会自动更新存储型生成列的数据。

注意:PostgreSQL 目前仅实现了存储型生成列。

定义生成列

通常,你可以在创建表时,使用下面的语法定义生成列:

CREATE TABLE table_name(
   ...,
   colum_name type GENERATED ALWAYS AS (expression ) STORED | VIRTUAL,
   ...
);

在此语法中:

  • column_name:指定生成列的名称。
  • type:指定列的数据类型。
  • expression:提供一个返回计算列值的表达式。
  • STORED关键字:表示生成列的数据物理存储在表中。
  • VIRTUAL关键字:表示生成列的数据是在查询时计算的,而不是物理存储的。

要给一个表添加一个生成列,可以使用 ALTER TABLE … ADD COLUMN 语句:

ALTER TABLE table_name
ADD COLUMN column_name type GENERATED ALWAYS AS (expression) STORED;

在为生成列定义一个表达式时,请确保它满足以下要求:

  • 表达式只能使用不可变函数,不能涉及子查询或引用当前行以外的任何内容。例如,表达式不能使用 CURRENT_TIMESTAMP 函数。
  • 表达式不能引用另一个生成列或系统列,但tableoid除外。

生成列不能带有默认值或标识定义。此外,它不能是分区键的一部分。

PostgreSQL 生成列示例

让我们来探索一些使用生成列的示例。

1) 拼接列值

首先,创建一个名为contacts的新表:

CREATE TABLE contacts(
   id SERIAL PRIMARY KEY,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   full_name VARCHAR(101) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
   email VARCHAR(300) UNIQUE
);

第二步,将行插入contacts表。full_name列的值将从first_namelast_name列的值自动更新:

INSERT INTO contacts(first_name, last_name, email)
VALUES
   ('John', 'Doe', 'john.doe@rockdata.net'),
   ('Jane', 'Doe', 'jane.doe@rockdata.net')
RETURNING *;

输出:

 id | first_name | last_name | full_name |              email
----+------------+-----------+-----------+---------------------------------
  1 | John       | Doe       | John Doe  | john.doe@rockdata.net
  2 | Jane       | Doe       | Jane Doe  | jane.doe@rockdata.net
(2 rows)

2) 计算净价

首先,创建一个名为products的表,用于存储产品信息:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    list_price DECIMAL(10, 2) NOT NULL,
    tax DECIMAL(5, 2) DEFAULT 0,
    discount DECIMAL(5, 2) DEFAULT 0,
    net_price DECIMAL(10, 2) GENERATED ALWAYS AS ((list_price + (list_price * tax / 100)) - (list_price * discount / 100)) STORED
);

products表中,net_price列是一个生成列,其值是根据价目表价格、税费和折扣按以下公式计算的:

list_price = list_price + (list_price * tax / 100)) - (list_price * discount / 100)

然后,将行插入到products表中:

INSERT INTO products (name, list_price, tax, discount)
VALUES
    ('A', 100.00, 10.00, 5.00),
    ('B', 50.00, 8.00, 0.00),
    ('C', 120.00, 12.50, 10.00)
RETURNING *;

输出:

 id | name | list_price |  tax  | discount | net_price
----+------+------------+-------+----------+-----------
  1 | A    |     100.00 | 10.00 |     5.00 |    105.00
  2 | B    |      50.00 |  8.00 |     0.00 |     54.00
  3 | C    |     120.00 | 12.50 |    10.00 |    123.00
(3 rows)

总结

使用生成列在表中自动进行计算。