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



  • 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?



  • The Hint in the error message suggests that you have a column of the name "new.total" - containing the https://www.postgresql.org/docs/current/sql-keywords-appendix.html "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:

    • https://stackoverflow.com/a/20880247/939860



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2