postgres INSERT trigger fires even with ON CONFLICT DO NOTHING

Question

In a couple of SO answers (1, 2), it's suggested that INSERT triggers shouldn't fire if there's a conflict and ON CONFLICT DO NOTHING is in the triggering statement. Perhaps I've misunderstood, but it does not seem to be true in my experiments.

Here's my SQL, run on 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();

If I then run a couple of inserts:

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

I would have expected to see Called def() the first time, but not the next.

What am I getting wrong?

score 9 · Accepted Answer · edited Mar 01 '19 at 00:16

A BEFORE INSERT trigger runs before the conflict check. The trigger has an opportunity to change the inserted values, and it wouldn't make sense to check for collisions before this happens. Per 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.

An AFTER INSERT trigger will behave as you expect.

postgres INSERT trigger fires even with ON CONFLICT DO NOTHING

1 Answers1