Generated Columns | 什么是生成列

generated column，生成列，也叫计算列。利用当前行的数据或一定的生成规则，或者同时应用两者，来计算并得到新的数据列。与使用 excel 电子表格，为某个表格添加一个计算列类似。

生成列，主要是定义列的值生成方式。意味着列的数据是上生成函数或者生在规则的输入参数与生成逻辑共同决定的，生成列的特点有：

1. 生成列的值由同行其它列的数据与生成函数（规则）得到
2. 数据库 自动触发 管理调用生成列的生成逻辑

生成列值在哪里定义 #

生成列值的定义，是作为列的约束形式而存在，因此，使用声明生成逻辑之前，要先定义列位置、数据类型。数据库用于生成列值的方式：

1. 在 ddl 中，使用 generated 关键字来指定列的生成逻辑
2. 声明 default，定义当无设定插入值时的，数据库自动赋值（有悟将此 default 归为生成列值较为勉强， 它从概念上并不是生成列，只是为列定义默认值）

摘自 postgres sql create table 的语法:

column_constraint:
  ...
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  ...

各数据库的语法上有一定差异，使用时请注意查阅对应 SQL 手册

DEFAULT

使用与列数据类型相同的值作为默认值。比如性别字段 F 为女性、M 为男性，可以为该列指定默认值为 F；又如，名为新增时间的日期类字段，使用当前日期或时间戳为其指定默认值，一般数据库都提供了这样的全局变量（CURRENT_DATE、CURRENT_TIMESTAMP）或函数 (current_date()、current_timestamp())。

GENERATED ALWAYS as (...) stored

为列定义生成表达式。这个表达式中可引用其它的普通列。stored 表示是生成列与普通列的值是保存在一起的。在 sqlite3 中，还有 virtual 不占用空间的版本。

-- postgres
drop table a;
create table a
( colc varchar(10)
, coli int
, gc   varchar(20) generated always as (colc || ' today') stored
, gi   int generated always as (coli * 2) stored
);

insert into a (colc, coli)
values ('youwu ', 1);

select * from a

例中分别演示了字符类型、数字类型的生成列 gc、gi，它们的值分别引用 colc、coli。结果如下：

  colc  | coli |      gc      | gi
--------+------+--------------+----
 youwu  |    1 | youwu  today |  2

GENERATED always as identify

使用序列号为数据行生成一个唯一标识序号。序列号是数字类型，因此该列也只定义为 int 类。

-- test.sql
-- postgres
drop table if exists b;
create table b
( col1 varchar(10) generated always as identity
, col2 varchar(20)
);
create table b
( col1 int generated always as identity
, col2 varchar(20)
);
insert into b(col1, col2) values(1,'youwu today');
insert into b(col2) values ('youwu today');
select * from b;

➜  psql -e < test.sql
drop table if exists b;
DROP TABLE
create table b
( col1 varchar(10) generated always as identity
, col2 varchar(20)
);
ERROR:  identity column type must be smallint, integer, or bigint
create table b
( col1 int generated always as identity
, col2 varchar(20)
);
CREATE TABLE
insert into b(col1, col2) values(1,'youwu today');
ERROR:  cannot insert a non-DEFAULT value into column "col1"
DETAIL:  Column "col1" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
insert into b(col2) values ('youwu today');
INSERT 0 1
select * from b;
 col1 |    col2
------+-------------
    1 | youwu today
(1 row)

从例postgres SQL的执行结果可以看到，使用 generated always as identity 的 列要定义为 int 族类型；并且，往里面插入值时，不能指定其他值，只能由数据库自动管理。

GENERATED by default as identify

与 GENERATED always as identify 类似，区别在于 by default 的非强制性。

-- postgres
drop table if exists c;
create table c
( col1 int generated by default as identity
, col2 varchar(20)
);
insert into c(col1, col2) values(1,'youwu today');
insert into c(col2) values ('youwu today');
insert into c(col2) values ('youwu today');
select * from c;

➜  psql -e < test.sql
drop table if exists c;
NOTICE:  table "c" does not exist, skipping
DROP TABLE
create table c
( col1 int generated by default as identity
, col2 varchar(20)
);
CREATE TABLE
insert into c(col1, col2) values(1,'youwu today');
INSERT 0 1
insert into c(col2) values ('youwu today');
INSERT 0 1
insert into c(col2) values ('youwu today');
INSERT 0 1
select * from c;
 col1 |    col2
------+-------------
    1 | youwu today
    1 | youwu today
    2 | youwu today
(3 rows)

使用 by default 与定义默认值类似，注意与 always 的区别。另，虽定义了 identity 只是声明了生成唯一值的序列号，并不是定义了表的主键，故而手动插入与序列相同值是被允许的，不要与主键约束混淆了。

生成列值产生的时机 #

生成列的值产生与 always、default、引用的对象有关。

何时使用生成列 #

平常最常见的是 default 默认值， generated always as 则较少见，有可能 as identity 在项目中还被禁止使用。我们还是要有区别的看待问题，不能人云亦云。

生成列是数据库机制自动管理，像 default、as (生成表达式) 用于为列定义添加列约束，而 as identity 是为了方便生成一列唯一的序号，此时往往会导入序列。 若基于数据库的应用比较重，或者唯一编号由应用程序自行生成或者控制，此时使用 as identity 则不太合适了；倘若你的应用属于轻量应用，无特殊考虑，那么使用 as identity 还是很方便的，只是多了一个序列号对象的管理而已。

各数据库中的生成列 #

文档:

• postgres: https://www.postgresql.org/docs/current/ddl-generated-columns.html
• sqlite: https://www.sqlite.org/gencol.html
• mysql: https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html

某数据库版本还不支持生成器怎么办？ #

1. 使用视图

create view 视图名称 as
select  ..., 生成列...
from    表

2. 触发器

利用触发器，在新增插入、修改时，修改列的值

3. 手工修改

当数据量较大时，数据表的物理一般会将约束条件去除，使用额外的 DML 来保证数据本身的约束逻辑。它大多出现在数据开发领域。在数据处理过程中，数据质量的问题有时会被暂时忽略，这时硬约束会阻止数据入表落地，与实际需要冲突。

⚠️ 开卷：

1. 你能不看SQL手册，完整的写出一个建表语句吗？包含本文提及的 default、generated always、as identity。
2. postgres 是如何管理序列的，它的信息保存在哪里？或者你最熟悉数据库它有序列吗？
3. 序列有哪里可以定义的参数？它可以使用在建表语句中吗？
4. 写一个包含 generated always as identity 的建表语句，并且指定序列参数？
5. 学习如何使用触发器