Postgres trigger to only execute procedure when NEW.column = true

Question

Using PostgreSQL - I'm trying to fire a procedure that runs a trigger only when the NEW (inserted or updated) row has the is_default column value set to boolean true.

This is what I've tried but I get a syntax error on my WHEN line:

CREATE TRIGGER trigger_price_group_default_price_handler
    BEFORE UPDATE OR INSERT
    ON price_groups
    WHEN (NEW.is_default = true)
    EXECUTE PROCEDURE clear_default_price_group();

Any ideas?

Are you on a PostgreSQL version from before conditional triggers were introduced, but reading documentation for a newer version? SELECT version(). — Craig Ringer
It's always a good idea to provide your version of Postgres. Helps to clarify a lot. — Erwin Brandstetter

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2015-04-22T05:56:05

Seems to be an oversight: FOR EACH ROW is missing:

CREATE TRIGGER trigger_price_group_default_price_handler
 BEFORE UPDATE OR INSERT ON price_groups
 FOR EACH ROW
 WHEN (NEW.is_default)
 EXECUTE PROCEDURE clear_default_price_group();

If you don't provide it, per documentation:

If neither is specified, FOR EACH STATEMENT is the default.

Bold emphasis mine. And you cannot reference NEW or OLD in a statement-level trigger, which makes the WHEN clause (referencing NEW) a syntax error - in any version of Postgres:

Statement-level triggers can also have WHEN conditions, although the feature is not so useful for them since the condition cannot refer to any values in the table.

Other than that, you need at least Postgres 9.0 to use a WHEN clause in CREATE TRIGGER

I think it's a bit unfortunate that the rare case FOR EACH STATEMENT is the default. Must be for historic reasons, I assume.

Aside: the expression NEW.is_default = true is just a noisy way of saying NEW.is_default. A boolean value can be used as expression directly.

Postgres trigger to only execute procedure when NEW.column = true

2 Answers