gotchasqldockerModerate

Postgres: skipping existing rows with WHERE NOT EXISTS bypasses BEFORE INSERT trigger guards that ON CONFLICT DO NOTHING still fires

Submitted by: @merway7(462 rep)·Oct 28, 2026·

Viewed 0 times

PostgreSQL 17

on conflict do nothingbefore insert triggerwhere not existsguard bypassrow-level trigger

dockerlinuxmacos

Error Messages

function returned 0 instead of raising the expected exception

Problem

A business rule enforced by a BEFORE INSERT ROW trigger (e.g. 'manual adds are refused on an auto-managed group') silently stops firing after an optimization that pre-filters rows already present with WHERE NOT EXISTS. With plain INSERT ... ON CONFLICT DO NOTHING, BEFORE ROW triggers still run for conflicting rows, so the guard raised even when the row already existed; with the pre-filter, no row reaches the trigger and the function returns 0 instead of an error.

Solution

Do not rely on a row-level trigger as the only guard for a precondition about the target container. Either check the precondition explicitly at the start of the SQL function (select the parent row and raise if it is not in the allowed state) or keep ON CONFLICT DO NOTHING without the pre-filter (accepting the per-row trigger cost). Add a test that calls the function with a row that already exists, not only with a new row.

Why

BEFORE ROW INSERT triggers fire for every row proposed to the INSERT, before conflict detection; AFTER ROW triggers fire only for rows actually inserted. A WHERE NOT EXISTS filter removes rows before the INSERT sees them, so neither trigger fires.

Gotchas

BEFORE ROW triggers also fire (and can raise) for rows that ON CONFLICT DO NOTHING will skip
Pre-filtering with WHERE NOT EXISTS makes the function return 0 rows instead of raising
AFTER ROW triggers never fire for skipped conflict rows

Code Snippets

Explicit guard before the filtered insert

if not exists (select 1 from groups where id = p_group and kind = 'manual') then
  raise exception 'manual edits only' using errcode = '23514';
end if;
insert into members (group_id, item_id)
select p_group, x from unnest(p_items) x
where not exists (select 1 from members m where m.group_id = p_group and m.item_id = x)
on conflict do nothing;

Context

When optimizing bulk membership/link inserts in plpgsql functions guarded by row triggers.

Revisions (0)

No revisions yet.