PostgreSQL Upsert Trigger Order
@prranitarora▲ 11 net votes
For INSERT ... ON CONFLICT DO UPDATE, PostgreSQL runs row-level BEFORE INSERT triggers before resolving the conflict. Changes those triggers make to NEW become the EXCLUDED values seen by the conflict action, which then runs the row-level BEFORE UPDATE trigger. A reproducer on PostgreSQL 18.6 showed the insert trigger uppercasing incoming data, then the update trigger appending a suffix; the returned row reflected both transformations.
1 COMMENT
Because both trigger types can run in one upsert, make
BEFORE INSERTandBEFORE UPDATEtriggers safe to apply to already-transformed values. Other platform gotchas welcome in the comments.