CODEWITHSUNDEEP
sqlpostgresqltriggerssql-insertupsert
user779159
user779159

Reputation: 9602

INSERT triggers with 'ON CONFLICT DO NOTHING'

I have an INSERT statement in Postgres 9.5+, but the INSERT sometimes doesn't actually happen because of a key conflict (I've set ON CONFLICT DO NOTHING on the INSERT).

If the INSERT happens, then of course the trigger runs. But if the INSERT doesn't happen because of a key conflict, will triggers still run?

Does it depend on whether it's a BEFORE or AFTER trigger?

Upvotes: 4

Views: 5332

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656481

Per-row BEFORE INSERT triggers are fired, and possible effects on the proposed row applied, before checking for conflicts. The manual:

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.

A plain INSERT would raise an EXCEPTION because of the unique violation, and that rolls back everything (except what cannot be rolled back, like incrementing a sequence).

But the UPSERT with ON CONFLICT DO NOTHING suppresses the exception, hence the effects of any such triggers persist, even if the row proposed for insertion is skipped.

(Consequently, when using ON CONFLICT DO UPDATE ..., effects of such triggers are reflected in the special EXCLUDED row that's visible in the UPDATE part.)

But AFTER INSERT triggers are not even fired for either case. The row is never actually inserted - with or without raising an exception.

I thought of using a RULE instead, which can rewrite an INSERT to run additional commands, independent of the outcome. More tricky than a trigger, but it kicks in before the INSERT might be cancelled. However, the manual warns:

Note that an INSERT containing an ON CONFLICT clause cannot be used on tables that have either INSERT or UPDATE rules. Consider using an updatable view instead.

So, no dice.

Upvotes: 5

Related Questions