2

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?

1
  • Start with your actual table definition (CREATE TABLE script) and Postgres version. May 15 at 5:47

1 Answer 1

2

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:

2
  • Hi Erwin, Thank you for your response. I had to enclose my column name within double quotes and it finally it worked. I only had to do NEW."total"
    – Digant
    May 16 at 12:19
  • @Digant: That doesn't seem to make sense. total is not a reserved word and does not require double quotes. If you provided your exact table definition (CREATE TABLE statement), like you should in any case, this would be easy. May 16 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.