ERROR: column "new" of relation xyz does not exist in postgresql Trigger

Question

I am trying to generate a code for column tpl_league_code using a trigger after insert into the tpl_league_tbl table. New entry is inserted first and then the update should occur but I get an error saying column new of relation tpl_league_tbl doesn't exist.

Here is my function script

CREATE OR REPLACE FUNCTION createLeagueCode()
RETURNS trigger AS
$BODY$
DECLARE
  leagueCode character varying(25);
BEGIN
  leagueCode := 'LEAUGECODE'||(SELECT COUNT(*) FROM tpl_league_tbl)||
(SELECT CAST (NOW() AS CHARACTER VARYING(10)));
  UPDATE tpl_league_tbl SET new.tpl_league_code=leagueCode;
  RETURN new;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

This is my trigger

CREATE TRIGGER createLeagueTrigger
AFTER INSERT
ON tpl_league_tbl
FOR EACH ROW
EXECUTE PROCEDURE createLeagueCode();

score 1 · Accepted Answer · answered May 06 '18 at 07:20

The UPDATE statement cannot work because table tpl_league_tbl does not have a column new. You would have to omit the new..

But you should not do this in anAFTER trigger, you should do it in a BEFORE trigger like this:

NEW.tpl_league_code := leagueCode;

Then when you RETURN NEW;, the new row has been modified before it is inserted, which is what you want. It is cumbersome and expensive to insert a row only to update it a split second later.

Another thing: You should not run

SELECT count(*) FROM tpl_league_tbl;

inside the trigger function, because this is a very expensive operation that requires a sequential table scan.

If you need a random, unique suffix for leagueCode, I recommend that you use a sequence and get the number with nextval. That will be much cheaper.

thanks for the answer but i am using hibernate to insert into the table.is there a way to achieve this in hibernate ? — Sohum Prabhudesai, May 06 '18 at 07:33
Huh? That has nothing to do with Hibernate, it's all inside the database. — Laurenz Albe, May 06 '18 at 07:35

score 0 · Answer 2 · answered May 06 '18 at 07:27

0

You don't need a trigger for such a simple task. It's overkill. Just create a sequence and use it instead of using count. Something like this:

CREATE SEQUENCE league_code START 1;

INSERT INTO tpl_league_tbl (..., leagueCode)
VALUES (..., 'LEAUGECODE' || nextval('league_code') || now()::varchar(10))

answered May 06 '18 at 07:27

Abbas Mashayekh

301
3
12

i am using hibernate to insert into tables,is there any way to achieve this in hibernate? – Sohum Prabhudesai May 06 '18 at 07:32
I don't have any experience with Hibernate, but I'm pretty sure it supports a lot of things, and if not you could always use a raw query like this question here: https://stackoverflow.com/questions/3707098/hibernate-custom-insert-into-database – Abbas Mashayekh May 06 '18 at 07:34

ERROR: column "new" of relation xyz does not exist in postgresql Trigger

2 Answers2