Only perform update if column exists

Question

Is it possible to execute an update conditionally if a column exists? For instance, I may have a column in a table and if that column exists I want that update executed, otherwise, just skip it (or catch its exception).

Fixing

Answer 1

You can do it inside a function. If you don't want to use the function later you can just drop it afterwards.

To know if a column exists in a certain table, you can try to fetch it using a select(or a perform, if you're gonna discard the result) in information_schema.columns.

The query bellow creates a function that searches for a column bar in a table foo, and if it finds it, updates its value. Later the function is run, then droped.

create function conditional_update() returns void as
$$
begin
  perform column_name from information_schema.columns where table_name= 'foo' and column_name = 'bar';
  if found then
    update foo set bar = 12345;
  end if;
end;
$$ language plpgsql;
select conditional_update();
drop function conditional_update();

Khz

Answer 2

With the following table as example :

CREATE TABLE mytable (
    idx INT
    ,idy INT
    );

insert into mytable values (1,2),(3,4),(5,6);

you can create a custom function like below to update:

create or replace function fn_upd_if_col_exists(_col text,_tbl text,_val int) returns void as 
$$
begin
If exists (select 1 
from information_schema.columns  
where table_schema='public' and table_name=''||_tbl||'' and column_name=''||_col||'' ) then
execute format('update mytable set '||_col||'='||_val||'');
raise notice 'updated';
else
raise notice 'column %s doesn''t exists on table %s',_col,_tbl;
end if;
end;
$$
language plpgsql

and you can call this function like:

select fn_upd_if_col_exists1('idz','mytable',111) -- won't update raise "NOTICE:  column idz deosnt exists on table mytables"

select fn_upd_if_col_exists1('idx','mytable',111) --will upadate column idx with value 1111 "NOTICE:  updated"

Ezzell