文章目录

• 基于常量表达式创建一个唯一索引
• 使用 INSERT ON CONFLICT 插入和更新
• 通过触发器禁止数据删除
• 总结

大家好，我是只谈技术不剪发的 Tony 老师。今天我们来讨论一个有趣的话题：如何在 PostgreSQL 中实现一个只能存储一行数据的表。

假如我们有一个表 t_version，用于记录应用系统的版本信息：

create table t_version(version text not null, update_at timestamp not null);

第一次安装应用程序时需要生成一条记录，以后升级系统时需要更新版本信息，但不允许用户删除该记录。这种需求该如何实现？

基于常量表达式创建一个唯一索引

在 PostgreSQL 中想要限制表中只能包含一行数据实际上非常简单，就是利用表达式索引（也叫函数索引）基于常量值创建一个唯一索引。

针对上面的问题，我们可以为表 t_version 创建一个唯一索引：

create unique index t_version_uk on t_version ( (1) );

索引 t_version_uk 是一个基于常量表达式 (1) 的函数索引，并且具有唯一性。也就是说，表中任何数据行对应的索引值都是 1，而唯一索引只允许一个 1，因此该表中最多只能存储一行数据。

使用 INSERT ON CONFLICT 插入和更新

第一次插入数据时可以使用 INSERT 语句，但是如果已经存在数据时就会返回错误：

-- 初始化安装
insert into t_version values ('系统版本 1.0.0', current_timestamp);

-- 升级软件版本
insert into t_version values ('系统版本 1.1.0', current_timestamp);
ERROR:  duplicate key value violates unique constraint "t_version_uk"
DETAIL:  Key ((1))=(1) already exists.

第二次插入数据时返回了唯一约束冲突。所以，如果系统进行了升级，就需要使用 UPDATE 语句更新版本信息：

UPDATE t_version
SET version = '系统版本 1.1.0',
    update_at = current_timestamp;

但是问题在于我们需要判断表中是否已经存在数据，然后执行不同的语句。为了解决这个问题，可以使用 INSERT ON CONFILCT 语句，也称为 UPSERT 语句：

-- 清除数据
truncate table t_version;

-- 初始化安装
insert into t_version values ('系统版本 1.0.0', current_timestamp);
on conflict ((1)) 
do update set version = excluded.version, 
              update_at = excluded.update_at;
 
select * from t_version;
version      |update_at          |
-------------|-------------------|
系统版本 1.0.0|2020-07-02 21:57:06|

-- 升级软件版本
insert into t_version values ('系统版本 1.1.0', current_timestamp)
on conflict ((1)) 
do update set version = excluded.version, 
              update_at = excluded.update_at;

select * from t_version;
version      |update_at          |
-------------|-------------------|
系统版本 1.1.0|2020-07-02 21:58:55|

通过使用 ON CONFLICT 选项，可以使用相同的 INSERT 语句实现数据插入和更新。

通过触发器禁止数据删除

最后一个问题就是需要避免版本信息被误删除，这个可以通过一个触发器来实现。首先，创建一个触发器函数：

CREATE OR REPLACE FUNCTION version_del_func()
  RETURNS trigger
AS $$
BEGIN
  RAISE '禁止删除版本信息！';
END; $$
LANGUAGE plpgsql;

该函数直接返回了一个异常错误信息。然后为 t_version 表创建一个删除触发器：

CREATE TRIGGER tri_version_del 
BEFORE DELETE ON t_version
FOR EACH STATEMENT
EXECUTE FUNCTION version_del_func();

触发器是一个语句级 BEFORE 触发器，在任何删除语句之前调用函数 version_del_func 返回错误信息。

我们执行以下语句删除版本信息：

delete
from t_version;
ERROR: 禁止删除版本信息！
  Where: PL/pgSQL function version_del_func() line 3 at RAISE

select * from t_version;
version      |update_at          |
-------------|-------------------|
系统版本 1.1.0|2020-07-02 21:58:55|

删除语句返回了错误信息，t_version 中的数据仍然存在。不过需要注意的是，TRUNCATE TABLE 语句仍然可以清除表中的数据，因为它不会触发 DML 触发器。

总结

在 PostgreSQL 中索引列不一定是表中的字段，也可以是基于表的一个或多个字段的函数或标量表达式，甚至可以是一个常量表达式。该功能通常用于提高使用表达式作为过滤条件时的查询性能，本文演示了它的另一种特殊的用途。

除了本文使用的函数索引，还有没有其他的实现方法？欢迎关注️、评论、点赞