防止在没有触发器的情况下连续重复值

Question

在一个组中，我想防止连续重复值的INSERTs，其中"连续的"；由一个简单的CCD_ 2子句定义。

想象一下，一组实验定期对传感器的值进行采样。我们只想插入一个值，如果它是该实验的新。

请注意，的旧值允许重复。所以这是允许的：

id    experiment    value
1             A       10
2             A       20
3             A       10

但这不是：

id    experiment    value
1             A       10
2             A       10

我知道如何找到每个实验的先前值：

SELECT
*,
lag(sample_value) OVER experiment_and_id
FROM new_samples
WINDOW experiment_and_id AS (
PARTITION BY experiment
ORDER BY id
);

从文档中我知道CHECK约束不允许在检查中使用其他行：

PostgreSQL不支持CHECK约束，该约束引用要检查的新行或更新行以外的表数据。虽然违反此规则的CHECK约束可能在简单测试中起作用，但它不能保证数据库不会达到约束条件为false的状态(由于随后涉及的其他行的更改(。这将导致数据库转储和重新加载失败。即使在完整的数据库状态与约束一致的情况下，重新加载也可能失败，因为没有按照满足约束的顺序加载行。如果可能，请使用UNIQUE、EXCLUDE或FOREIGN KEY约束来表示跨行和跨表限制。

如果您希望在行插入时对其他行进行一次性检查，而不是持续维护一致性保证，那么可以使用自定义触发器来实现这一点。(这种方法避免了转储/重新加载问题，因为pg_dump直到重新加载数据后才重新安装触发器，因此在转储/重新装载过程中不会强制执行检查。(

EXCLUDE约束看起来很有希望，但主要用于测试不相等的情况。我不确定是否可以在其中包含窗口函数。

所以我只剩下一个自定义触发器，但对于一个相当常见的用例来说，这似乎有点像黑客攻击。

有人能改进扳机的使用吗？

理想情况下，我只想说：

INSERT ....
ON CONFLICT DO NOTHING

让Postgres来处理剩下的！

---

最小工作示例

BEGIN;
CREATE TABLE new_samples (
id INT GENERATED ALWAYS AS IDENTITY,
experiment VARCHAR,
sample_value INT
);
INSERT INTO new_samples(experiment, sample_value)
VALUES
('A', 1),
-- This is fine because they are for different groups
('B', 1),
-- This is fine because the value has changed
('A', 2),
-- This is fine because it's different to the previous value in
-- experiment A.
('A', 1),
-- Two is not allowed here because it's the same as the value
-- before it, within this experiment.
('A', 1);
SELECT
*,
lag(sample_value) OVER experiment_and_id
FROM new_samples
WINDOW experiment_and_id AS (
PARTITION BY experiment
ORDER BY id
);
ROLLBACK;

Answer 1

如果样本不会更改，那么文档中引用的限制将与您的用例无关。

您可以创建一个函数来实现这一点：

create or replace function check_new_sample(_experiment text, _sample_value int)
returns boolean as 
$$
select _sample_value != first_value(sample_value) 
over (partition by experiment 
order by id desc) 
from new_samples
where experiment = _experiment;
$$ language sql;
alter table new_samples add constraint new_samples_ck_repeat 
check (check_new_sample(experiment, sample_value));

插入示例：

insert into new_samples (experiment, sample_value) values ('A', 1);
INSERT 0 1
insert into new_samples (experiment, sample_value) values ('B', 1);
INSERT 0 1
insert into new_samples (experiment, sample_value) values ('A', 2);
INSERT 0 1
insert into new_samples (experiment, sample_value) values ('A', 1);
INSERT 0 1
insert into new_samples (experiment, sample_value) values ('A', 1);
ERROR:  new row for relation "new_samples" violates check constraint "new_samples_ck_repeat"
DETAIL:  Failing row contains (5, A, 1).