Postgresql 中文操作指南

5.3. Generated Columns #

生成的列是一个特殊列，它始终根据其他列计算出来。因此，它对于列而言就相当于视图对于表格而言。有两种类型的生成列：存储和虚拟。存储生成的列在写入（插入或更新）时计算，并占用存储空间，就好像它是普通列一样。虚拟生成的列不占用存储空间，并在读取时计算。因此，虚拟生成的列类似于视图，而存储生成的列类似于物化视图（但它始终会自动更新）。PostgreSQL 目前仅实现存储生成的列。

A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables. There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically). PostgreSQL currently implements only stored generated columns.

若要创建生成的列，请在 CREATE TABLE 中使用 GENERATED ALWAYS AS 子句，例如：

To create a generated column, use the GENERATED ALWAYS AS clause in CREATE TABLE, for example:

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

必须指定关键字 STORED 来选择生成的列的存储种类。有关更多详细信息，请参阅 CREATE TABLE 。

The keyword STORED must be specified to choose the stored kind of generated column. See CREATE TABLE for more details.

不能直接写入生成的列。在 INSERT 或 UPDATE 命令中，无法为生成的列指定值，但可以指定 DEFAULT 关键字。

A generated column cannot be written to directly. In INSERT or UPDATE commands, a value cannot be specified for a generated column, but the keyword DEFAULT may be specified.

考虑具有默认值和生成的列之间的区别。如果未提供其他值，则会在首次插入行时对列默认值进行一次计算；只要行更改且无法覆盖，就会更新生成的列。列默认值可能不会引用表格的其他列；生成表达式通常会这样做。列默认值可以使用不稳定函数，例如 random() 或引用当前时间的函数；这不适用于生成的列。

Consider the differences between a column with a default and a generated column. The column default is evaluated once when the row is first inserted if no other value was provided; a generated column is updated whenever the row changes and cannot be overridden. A column default may not refer to other columns of the table; a generation expression would normally do so. A column default can use volatile functions, for example random() or functions referring to the current time; this is not allowed for generated columns.

对涉及生成列的生成列和表格的定义适用于一些限制：

Several restrictions apply to the definition of generated columns and tables involving generated columns:

对使用生成列的应用程序适用其他注意事项。

Additional considerations apply to the use of generated columns.