PostgreSQL 如何實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        基于常量表達(dá)式創(chuàng)建一個(gè)唯一索引
        使用 INSERT ON CONFLICT 插入和更新
        通過(guò)觸發(fā)器禁止數(shù)據(jù)刪除
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們來(lái)討論一個(gè)有趣的話題：如何在 PostgreSQL 中實(shí)現(xiàn)一個(gè)只能存儲(chǔ)一行數(shù)據(jù)的表。

    ??如果你使用的是 MySQL，可以通過(guò)這篇文章了解如何在 MySQL 中實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表。
    ??如果你使用的是 Oracle，可以通過(guò)這篇文章了解如何在 Oracle 中實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表。

假如我們有一個(gè)表 t_version，用于記錄應(yīng)用系統(tǒng)的版本信息：

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



第一次安裝應(yīng)用程序時(shí)需要生成一條記錄，以后升級(jí)系統(tǒng)時(shí)需要更新版本信息，但不允許用戶刪除該記錄。這種需求該如何實(shí)現(xiàn)？
基于常量表達(dá)式創(chuàng)建一個(gè)唯一索引

在 PostgreSQL 中想要限制表中只能包含一行數(shù)據(jù)實(shí)際上非常簡(jiǎn)單，就是利用表達(dá)式索引（也叫函數(shù)索引）基于常量值創(chuàng)建一個(gè)唯一索引。

針對(duì)上面的問(wèn)題，我們可以為表 t_version 創(chuàng)建一個(gè)唯一索引：

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

 

索引 t_version_uk 是一個(gè)基于常量表達(dá)式 (1) 的函數(shù)索引，并且具有唯一性。也就是說(shuō)，表中任何數(shù)據(jù)行對(duì)應(yīng)的索引值都是 1，而唯一索引只允許一個(gè) 1，因此該表中最多只能存儲(chǔ)一行數(shù)據(jù)。??
使用 INSERT ON CONFLICT 插入和更新

第一次插入數(shù)據(jù)時(shí)可以使用 INSERT 語(yǔ)句，但是如果已經(jīng)存在數(shù)據(jù)時(shí)就會(huì)返回錯(cuò)誤：

-- 初始化安裝
insert into t_version values ('系統(tǒng)版本 1.0.0', current_timestamp);

-- 升級(jí)軟件版本
insert into t_version values ('系統(tǒng)版本 1.1.0', current_timestamp);
ERROR:  duplicate key value violates unique constraint "t_version_uk"
DETAIL:  Key ((1))=(1) already exists.

 

第二次插入數(shù)據(jù)時(shí)返回了唯一約束沖突。所以，如果系統(tǒng)進(jìn)行了升級(jí)，就需要使用 UPDATE 語(yǔ)句更新版本信息：

UPDATE t_version
SET version = '系統(tǒng)版本 1.1.0',
    update_at = current_timestamp;


但是問(wèn)題在于我們需要判斷表中是否已經(jīng)存在數(shù)據(jù)，然后執(zhí)行不同的語(yǔ)句。為了解決這個(gè)問(wèn)題，可以使用 INSERT ON CONFILCT 語(yǔ)句，也稱為 UPSERT 語(yǔ)句：

-- 清除數(shù)據(jù)
truncate table t_version;

-- 初始化安裝
insert into t_version values ('系統(tǒng)版本 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          |
-------------|-------------------|
系統(tǒng)版本 1.0.0|2020-07-02 21:57:06|

-- 升級(jí)軟件版本
insert into t_version values ('系統(tǒng)版本 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          |
-------------|-------------------|
系統(tǒng)版本 1.1.0|2020-07-02 21:58:55|


通過(guò)使用 ON CONFLICT 選項(xiàng)，可以使用相同的 INSERT 語(yǔ)句實(shí)現(xiàn)數(shù)據(jù)插入和更新。
通過(guò)觸發(fā)器禁止數(shù)據(jù)刪除

最后一個(gè)問(wèn)題就是需要避免版本信息被誤刪除，這個(gè)可以通過(guò)一個(gè)觸發(fā)器來(lái)實(shí)現(xiàn)。首先，創(chuàng)建一個(gè)觸發(fā)器函數(shù)：

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

    
該函數(shù)直接返回了一個(gè)異常錯(cuò)誤信息。然后為 t_version 表創(chuàng)建一個(gè)刪除觸發(fā)器：

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

    
觸發(fā)器是一個(gè)語(yǔ)句級(jí) BEFORE 觸發(fā)器，在任何刪除語(yǔ)句之前調(diào)用函數(shù) version_del_func 返回錯(cuò)誤信息。

我們執(zhí)行以下語(yǔ)句刪除版本信息：

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

select * from t_version;
version      |update_at          |
-------------|-------------------|
系統(tǒng)版本 1.1.0|2020-07-02 21:58:55|

    
刪除語(yǔ)句返回了錯(cuò)誤信息，t_version 中的數(shù)據(jù)仍然存在。不過(guò)需要注意的是，TRUNCATE TABLE 語(yǔ)句仍然可以清除表中的數(shù)據(jù)，因?yàn)樗粫?huì)觸發(fā) DML 觸發(fā)器。
總結(jié)

在 PostgreSQL 中索引列不一定是表中的字段，也可以是基于表的一個(gè)或多個(gè)字段的函數(shù)或標(biāo)量表達(dá)式，甚至可以是一個(gè)常量表達(dá)式。該功能通常用于提高使用表達(dá)式作為過(guò)濾條件時(shí)的查詢性能，本文演示了它的另一種特殊的用途。