Clever Geek Handbook

Update only if column exists

Can the update be done conditionally if the column exists? For example, I might have a column in a table and if that column exists I want this update to be done, otherwise just skip it (or catch its exception).

+3


source to share


2 answers


You can do this inside a function. If you don't want to use this feature later, you can simply leave it for later.

To find out if a column exists in a specific table, you can try to fetch it with select (or execute if you are going to discard the result) in information_schema.columns

.



The query below creates a function that looks for the bar column in the foo table , and if it finds one, updates its value. Later, the function is started and then unloaded.

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();
0


source


See the following table as an 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:



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"
0


source






More articles:


All Articles