PostgreSQL标识列及存储列示例

PostgreSQL里的自动生成标识列identity column和自动生成存储列generated column是非常不错的功能，不过他们的实现语法比较近似，容易弄混，本文将进行示例介绍。

PG v10 : identity column

PostgreSQL v10 版本引入了identity column， 功能和 serial 很像，它是对SQL兼容性的提升，并且修复了 serial 类型存在的以下问题：

1. CREATE TABLE / LIKE 命令复制表时指定相同的序列
2. 不能使用 ALTER TABLE 增加或删除 serialness
3. 表删除 default 属性时不会删除序列
4. 需要对序列进行额外赋权

常见的使用场景是自动生成主键序列，有两种使用形式

• generated always as identity声明的字段，用户不能显式赋值插入，否则会报错。
• generated by default as identity声明的字段，用户可以覆盖系统的赋值，手工插入

声明之后insert语句也可以通过以下方式来覆盖上述限制：

• overriding system value
• overriding user value

使用overriding system value为generated always的标识列指定显式值

postgres=# create table gen1(
postgres(# c1 int generated always as identity,
postgres(# c2 varchar(10)
postgres(# );
CREATE TABLE
postgres=# 
postgres=# insert into gen1 (c2) values('data1');
INSERT 0 1
postgres=# 
postgres=# select * from gen1;
 c1 |  c2   
----+-------
  1 | data1
(1 row)

postgres=# insert into gen1 
postgres-# overriding system value              
postgres-# values (100, 'data2') ;
INSERT 0 1
postgres=# select * from gen1;
 c1  |  c2   
-----+-------
   1 | data1
 100 | data2
(2 rows)
复制

使用overriding user value为generated by default的标识列提供的任何值都将被忽略，并使用默认的序列生成值：

postgres=# create table gen2(
postgres(# c1 int generated by default as identity,
postgres(# c2 varchar(10)
postgres(# );
CREATE TABLE
postgres=# insert into gen2 (c2) values('data1');
INSERT 0 1
postgres=# select * from gen2;
 c1 |  c2   
----+-------
  1 | data1
(1 row)

postgres=# insert into gen2 
postgres-# overriding user value 
postgres-# values (100, 'data2') ;
INSERT 0 1
postgres=# select * from gen2;
 c1 |  c2   
----+-------
  1 | data1
  2 | data2
(2 rows
复制

使用overriding user value为generated always的标识列忽略用户指定的显式值，并使用默认的序列生成值(PG v13开始支持)：

postgres=# create table gen3(
postgres(# c1 int generated always as identity,
postgres(# c2 varchar(10)
postgres(# );
CREATE TABLE
postgres=# insert into gen3 (c2) values('data1');
INSERT 0 1
postgres=# select * from gen3;
 c1 |  c2   
----+-------
  1 | data1
(1 row)

postgres=# insert into gen3 
postgres-# overriding user value 
postgres-# values (100, 'data2') ;
INSERT 0 1
postgres=# select * from gen3;
 c1 |  c2   
----+-------
  1 | data1
  2 | data2
(2 rows)
复制

PG v12 : generated column

PostgreSQL v12 版本引入了generated column的功能，支持自动生成字段的值。

• 基于当前表的当前行的其他列
• 基于表达式或者immutable函数

示例一：创建表gen4，c3列自动做加法运算

postgres=# create table gen4(
postgres(# c1 int, 
postgres(# c2 int, 
postgres(# c3 int generated always as (c1 + c2) stored
postgres(# );
CREATE TABLE
postgres=# insert into gen4 values(100,200);
INSERT 0 1
postgres=# select * from gen4;
 c1  | c2  | c3  
-----+-----+-----
 100 | 200 | 300
(1 row)

postgres=# update gen4 set c2=c2+1 where c1=100;
UPDATE 1
postgres=# select * from gen4;
 c1  | c2  | c3  
-----+-----+-----
 100 | 201 | 301
(1 row)
复制

示例二：自动更新时间戳
首先创建一个immutable函数

create or replace function im_now () returns timestamptz as $$  
  select CURRENT_TIMESTAMP;  
$$ language sql  immutable;  
复制

接着创建表gen5

create table gen5(
id int primary key, 
info text, 
crt_time timestamp, 
mod_time timestamp GENERATED ALWAYS AS ( im_now() ) stored
);  
复制

接着进行下面的测试：

postgres=# insert into gen5 values(1,'aaa',now());
INSERT 0 1
postgres=# select * from gen5;
 id | info |          crt_time          |          mod_time          
----+------+----------------------------+----------------------------
  1 | aaa  | 2022-04-27 12:55:09.247823 | 2022-04-27 12:55:09.247823
(1 row)
postgres=# update gen5 set info='bbb' where id=1;
UPDATE 1
postgres=# select * from gen5;
 id | info |          crt_time          |          mod_time          
----+------+----------------------------+----------------------------
  1 | bbb  | 2022-04-27 12:55:09.247823 | 2022-04-27 12:55:09.247823
(1 row)
复制

从上面可以看到，insert自动生成了数据，update时mod_time并没有更新时间戳。

我们再修改一下上面的函数定义：

create or replace function  public.im_now(VARIADIC "any")
 RETURNS timestamp with time zone
 LANGUAGE internal
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$now$function$;

复制

接着创建表gen6

create table gen6(
id int primary key, 
info text, 
crt_time timestamp, 
mod_time timestamp GENERATED ALWAYS AS (im_now(info)) stored
);  
复制

然后进行下面的测试：

postgres=# insert into gen6 values(1,'aaa',now());
INSERT 0 1
postgres=# 
postgres=# select * from gen6;
 id | info |          crt_time          |          mod_time          
----+------+----------------------------+----------------------------
  1 | aaa  | 2022-04-27 18:53:10.361791 | 2022-04-27 18:53:10.361791
(1 row)
postgres=# update gen6 set info='bbb' where id=1;
UPDATE 1
postgres=# select * from gen6;
 id | info |          crt_time          |          mod_time          
----+------+----------------------------+----------------------------
  1 | bbb  | 2022-04-27 18:53:10.361791 | 2022-04-27 18:53:48.498064
(1 row)
复制

可以看到对info列的修改会自动更新mod_time，如果其它字段也希望触发更新，可以在im_now函数传相应的参数即可。

保持联系

从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴加我微信，进群吹牛唠嗑，交流技术，互赞文章。