时间戳

首页 > 解决方案 > 如何在 PostgreSQL 中对行条目进行版本化？

sql - 如何在 PostgreSQL 中对行条目进行版本化？

问题描述

我创建了一个表格，列出了一堆销售人员及其信息。输入日期是输入数据的日期，到期日期应该跟踪数据何时被替换，有一个版本字段，一个串行自动生成的主键和一堆其他东西。

entry date | expiry date|ver|key|no     |name    |location  | product
2019-02-25 | 2199-12-31 | 1 | 1 | SP001 | Toby   | Bogota   |C
2019-02-25 | 2199-12-31 | 1 | 2 | SP002 | Janet  | Chicago  |M
2019-02-25 | 2199-12-31 | 1 | 3 | SP003 | Chepe  | Sinaloa  |H
2019-02-25 | 2199-12-31 | 1 | 4 | SP004 | Emilio | Colombia |E
2019-02-25 | 2199-12-31 | 1 | 5 | SP005 | Epifano| Texas    |C

我们以第一个条目为例：

entry date | expiry date|ver|key|no     |name    |location  | product    
2019-02-25 | 2199-12-31 | 1 | 1 | SP001 | Toby   | Bogota   |C

我希望当托比从波哥大搬到牙买加时，而不是更新他的位置，它会创建一个新行，这将是上述行的新版本。因此，如果我们在 26 日更改此设置，我们将有两行，例如：

entry date | expiry date|ver|key|no     |name    |location  | product    
2019-02-25 | 2019-02-26 | 1 | 1 | SP001 | Toby   | Bogota   |C
2019-02-26 | 2199-12-31 | 2 | 6 | SP001 | Toby   | Jamaica  |C

创建一个新行并将下一个序列作为主键。随着使用更新的列创建新版本，旧行到期。这样，我们仍然在数据库中保留旧行，而不是对其进行更新。

我试图通过使用和 UPSERT 来完成：

INSERT INTO testarea.salepersons_3(
        date_from, date_to, version, sp_s_key, sp_no, sp_name, sp_territory, 
        sp_product)
VALUES ('2019-02-26', '2199-12-31', 1, 1, 'SP001', 'Tobby', 'Jamaica', 
        'C')
ON CONFLICT (sp_s_key)
DO UPDATE 
SET date_from = '2019-02-26', date_to = '2199-12-31',version = version + 1 ,sp_no = 'SP001',sp_name='Tobby',sp_territory='Jamaica',sp_product='C'
where b.sp_s_key = 1

但是，这将在不保存原始版本的情况下进行更新。所有提示都表示赞赏。

标签： sqlpostgresql

解决方案

鲜为人知的“timetravel”扩展为 PostgreSQL 数据库带来了行级版本控制。

timetravel - 实现时间旅行的函数

很久以前，PostgreSQL 有一个内置的时间旅行功能，可以保存每个元组的插入和删除时间。这可以使用这些函数来模拟。[…]

在2014-2015 年的博客文章中，Paul Jolly 解释了它是如何完成的：

  • 在数据库中启用timetravel扩展。

  • 在需要此行为的每个表上定义名为valid_fromand的时间戳字段。valid_to

  • 查询该表时，如果当前时间在valid_fromto valid_to（包括 if valid_tois infinity）范围内，则该记录是最新的。

  • 在更新该记录时，根据当前记录的身份（可能是主键）定义触发器以使当前记录的有效性到期。

推荐阅读