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).

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