gpt4 book ai didi

postgresql - 即使在 ON CONFLICT DO NOTHING 情况下,postgres INSERT 触发器也会触发

转载 作者:行者123 更新时间:2023-11-29 11:32:33 31 4
gpt4 key购买 nike

在几个 SO 答案(12)中,建议如果存在冲突则不应触发 INSERT 触发器,ON CONFLICT DO NOTHING 在触发语句中。也许我理解错了,但在我的实验中似乎并非如此。

这是我的 SQL,在 Postgres 9.6 上运行。

CREATE TABLE t (
n text PRIMARY KEY
);

CREATE FUNCTION def() RETURNS trigger AS $$
BEGIN
RAISE NOTICE 'Called def()';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER deftrig BEFORE INSERT ON t FOR EACH ROW EXECUTE PROCEDURE def();

如果我然后运行几个插入:

testdb=> insert into t (n) values ('dummy') on conflict do nothing;
NOTICE: Called def()
INSERT 0 1
testdb=> insert into t (n) values ('dummy') on conflict do nothing;
NOTICE: Called def()
INSERT 0 0

我希望第一次看到 Called def(),但下一次不会。

我哪里错了?

最佳答案

BEFORE INSERT 触发器在冲突检查之前运行。触发器有机会更改插入的值,在此之前检查冲突是没有意义的。每the documentation:

Note that the effects of all per-row BEFORE INSERT triggers are reflected in excluded values, since those effects may have contributed to the row being excluded from insertion.

AFTER INSERT 触发器将按照您的预期运行。

关于postgresql - 即使在 ON CONFLICT DO NOTHING 情况下,postgres INSERT 触发器也会触发,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54935903/

31 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com