"column new.total does not exist" error while creating a trigger

Question

I have spent a lot of time investigating this error and couldn't find a solution.

I am trying to create a trigger function and a trigger and then call the procedure when the when () condition in the trigger evaluates to true. But I'm getting an error while creating the trigger:

ERROR:  column new.total does not exist
LINE 3:  FOR EACH ROW WHEN (new.total * 80 / 100 >= 80) EXECUTE PROCEDURE...
HINT:  Perhaps you meant to reference the column "new.total".

Trigger function:

CREATE OR REPLACE FUNCTION public.totalInventory_events()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
     PERFORM pg_notify('notification', row_to_json(NEW)::text);
     RETURN NEW;
END;
$function$

Trigger:

CREATE TRIGGER trigger_on_totalinventory
 AFTER UPDATE OR INSERT ON users
 FOR EACH ROW 
 WHEN (new.total * 80 / 100 >= 80)
 EXECUTE PROCEDURE totalInventory_events();

Why the error, and how to fix it?

Answer 1

The Hint in the error message suggests that you have a column of the name "new.total" - containing the reserved word "new", and a dot (!). The otherwise illegal identifier forced with double-quotes. A very unfortunate, misleading choice.

Your trigger definition would have to read:

...
WHEN (NEW."new.total" * 80 / 100 >= 80)
...

My standing advice is to use legal, lower-case, unquoted identifiers to avoid any such confusion. I would also call the function total_inventory_events(), accordingly.

See:

• Are PostgreSQL column names case-sensitive?