PostgreSQL - 自更新序列字段

Question

我有一个包含 3 个字段的表，seq 类型为 integer 和 tstamp 类型为 timestamp 和 txt 类型为 text。示例:

seq | tstamp              | txt
--------------------------------
1   | 2014-01-01 00:00:00 | A
2   | 2014-01-02 00:00:00 | B 
3   | 2014-01-03 00:00:00 | C

我想做什么:

如果插入下一行时间戳 -> 2014-01-02 12:00:00 发生，我想根据有序的时间戳更新我的 seq 字段

我想要的结果:

seq | tstamp              | txt
--------------------------------
1   | 2014-01-01 00:00:00 | A 
2   | 2014-01-02 00:00:00 | B
3   | 2014-01-02 12:00:00 | A
4   | 2014-01-03 00:00:00 | C

我知道我可以捕获插入触发器并按照 tstamp 顺序更新 seq 字段，但对于每天进行的百万次插入来说，它看起来效率很低。

有更好的方法吗？就像...某种我不知道的 self 更新序列类型...

为什么我需要这个？

我需要一个始终更新的序列字段，因为我想知道，如果我只搜索 txt 字段设置为 A 的记录，它们之间是否存在时间“漏洞”，或者它们在未过滤的表中顺序关闭

示例:

SELECT * FROM table WHERE txt = 'A';

结果:

seq | tstamp              | txt
--------------------------------
1   | 2014-01-01 00:00:00 | A 
3   | 2014-01-02 12:00:00 | A 

接收查询结果的应用程序只需将 seq 与之前的 seq(current seq != previous seq + 1)进行比较，以检测结果之间的“漏洞”。这就是为什么只有 tstamp 是不够的

Answer 1

您不需要数字排序来实现您的目标:您可以使用 lag() 和 lead() window functions 检测漏洞:

select *
from (
  select *,
    not (txt = (lag(txt) over (order by tstamp))) as hole_before,
    not (txt = (lead(txt) over (order by tstamp))) as hole_after
  from t
) t
where txt = 'B';

SQLFiddle

编辑:如果您的数据可以包含多个相等的 tstamp 值(请注意，默认情况下，时间戳字段的精度没有明确的限制)，并且您想要要获得对它们的固定顺序，请将 serial 添加到您的表中，并在您的窗口函数中使用 order by tstamp, id。没有它，对您的表的查询每次都可能返回不一致，f.ex:

   First query's result could be        Second query's result could be      2014-01-03 00:00:00 | A             2014-01-03 00:00:00 | B      2014-01-03 00:00:00 | B             2014-01-03 00:00:00 | A