Postgres INSERT triggers with 'ON CONFLICT DO NOTHING'

Question

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?

Answer 1

For an AFTER trigger there are more "opportunities" to cancel an INSERT (with other triggers) and thereby not fire the trigger. But this problem does not seem to depend on BEFORE or AFTER. If the row is skipped in an UPSERT command (INSERT ... ON CONFLICT DO NOTHING), then it is skipped before any ON INSERT triggers would fire.

You have to check the success of the INSERT and act upon the outcome - just like we discussed under your other question:

• Run SQL statements in PL/pgSQL only if a row doesn't exist

You got solutions for the problem there. If the commands in the trigger do not depend on the inserted row, you might just use a transaction with several separate commands - or wrap this in a function for convenience.

If the commands in the trigger depend on the inserted row, you want the involved row back in either case. Then you have a classical case of INSERT or SELECT ...

• Is SELECT or INSERT in a function prone to race conditions?

... and then use the result in additional commands. You might chain that with data-modifying CTEs:

• Insert inserted id to another table

---

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.