Update a column value with another column value by default

Question

I have a table like

+ Column1 | Column2 | Column3 + +---------|---------|---------+ + 1 | val.txt | val.txt + + 2 | test.xls| test.xls+ + 3 | abc.dwg | abc.dwg + + 4 | y.txt | y.txt + + 5 | kylk.txt| + ............................... +---------|---------|---------+

Now when there is an empty value in column 3 then by default it should add the value of column 2, can that be done within the same table column with default condition?

Thought of creating a function and trigger and I have tried by creating a function but failed.

CREATE OR REPLACE FUNCTION fun_gen() RETURNS trigger AS $BODY$BEGIN update public.tab_name set column3=column2 where column3 is null; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

Is there a way to give a default condition on table column itself or how to solve it function and trigger Am using postgres 9.4 in centos 7.

Thanks in advance

UPDATE in a trigger is dangerous. Use the NEW/OLD special variables instead. -- But consider not changing your table at all: it is very easy to query your table in the desired format: SELECT column1, column2, coalesce(column3, column2) FROM table1. — pozs
– pozs, Commented May 12, 2017 at 8:52

score 2 · Accepted Answer · 2017-05-12 10:48:04Z

If you want to change a column value during an update or insert do not use UPDATE in the trigger, change the NEW record that is passed to the trigger.

CREATE OR REPLACE FUNCTION fun_gen() RETURNS trigger AS $BODY$ BEGIN if new.column3 is null then new.column3 := new.column2; end if; return new; END $BODY$ LANGUAGE plpgsql VOLATILE COST 100;

In order to make this work you need a before trigger:

create trigger tab_name_trigger BEFORE update or insert on tab_name for each row execute procedure fun_gen();

Creating a view that simply does this during retrieval would be much more efficient.

create view tab_name_no_null as select column1, column2, coalesce(column3, column2) as column3 from tab_name;

The evaluation of coalesce() is cheap. The only situation where the trigger would be needed if you are searching on that column a lot and want to create an index on it.

Below is the error that getting white importing a csv file--- ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function fun_gen() line 3 at IF
@stacky: sorry, forgot to add the for each row option. This only works with a row level trigger
Hey thanks for the answer, its working fine, but as I import every csv file, its skipping the 1st record, was it due to this function and trigger or out of this pretext?
@stacky: that must be something with the way you import your data.

Collectives™ on Stack Overflow

Update a column value with another column value by default

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related