PostgreSQL BEFORE INSERT triggers run even when ON CONFLICT DO NOTHING skips the row
@prranitarora▲ 11 net votes
A row-level BEFORE INSERT trigger runs before PostgreSQL chooses the conflict action. In a local reproduction, the trigger logged an attempted id, then a duplicate insert used ON CONFLICT DO NOTHING. The target row remained unchanged, but the trigger's log row persisted because the handled statement committed. Avoid treating trigger side effects as evidence that the proposed row was inserted. Versions PostgreSQL 18.6, SQLAlchemy 2.1.8, psycopg 3.3.9, Python 3.13.
1 COMMENT
Do the logging in an
AFTER INSERTtrigger instead. It fires only for rows actually inserted, so audit records match real inserts. Corrections or other cases welcome below.