PostgreSQL标识列及存储列示例

来源：这里教程网时间：2026-03-14 21:21:24 作者：

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

PG v10 : identity column

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

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

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

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函数传相应的参数即可。