PostgreSQL DB Design IF column is NULL then COALESCE(COLUMN A, COLUMN B)

Question

I'm designing a PostgreSQL schema and I want to create a rule that if the user doesn't enter a value for Column C then i make the value of column C COALESCE(COLUMN A, COLUMN B). I've tried table update, and a trigger but there must be something wrong with the logic, I'd appreciate your help - thanks.

I have tried:

1.

update Mytable 
    set ColC=
    case when ColC is NULL then COALESCE(ColA,ColB)
    else ColC;

2.

CREATE TRIGGER ColC_VIOLATION
BEFORE INSERT OR UPDATE OF ColC
ON Mytable FOR EACH ROW
WHEN ColC is NULL then COALESCE(ColA,ColB)

3.

CREATE OR REPLACE FUNCTION ColC_func() RETURNS trigger AS 
$$
BEGIN
UPDATE Mytable
    set ColC=
    case when ColC is NULL then  COALESCE(ColA,ColB)
    else ColC ;
END
$$
LANGUAGE PLPGSQL;

Laurenz Albe · Accepted Answer · 2020-01-27 07:24:43Z

1

you don't need an UPDATE statement in the trigger, just assign the value:

CREATE OR REPLACE FUNCTION ColC_func() RETURNS trigger AS 
$$
BEGIN
   NEW.colc := COALESCE(NEW.cola, NEW.colb);
   RETURN NEW;
END
$$
LANGUAGE PLPGSQL;

And make the trigger only fire if colc is null:

CREATE TRIGGER ColC_VIOLATION
  BEFORE INSERT OR UPDATE on mytable
  FOR EACH ROW execute procedure colc_func()
  WHEN ColC is NULL;

edited Jan 27, 2020 at 7:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Jan 27, 2020 at 7:15

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL DB Design IF column is NULL then COALESCE(COLUMN A, COLUMN B)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related