SQL update multiple columns matching a pattern

Question

I have a table in a postgresql database in which I have multiple columns matching a pattern /\w*(?:_open|_close)/g. Those are timefields.

I would like to substract 1 hour from all the rows on those columns. Is there a way to do without running an UPDATE query separately for each column?

Answer 1

I think you're trying to avoid writing:

  UPDATE mytable SET
   door_open = door_open + INTERVAL '1' HOUR,
   door_close = door_close + INTERVAL '1' HOUR,
   car_open = car_open + INTERVAL '1' HOUR,
   car_close = car_close + INTERVAL '1' HOUR,
   ... blah blah ...
  WHERE
   ...

and instead want a way to match column names by wildcard.

If that is the case, then no, there is no built-in way to do that.

You can construct a query dynamically using PL/PgSQL: Use a query against the information_schema.columns view to get column names, then string concatenation to form the query. Then you can run it with EXECUTE. There are many examples of such dynamic SQL elsewhere on Stack Overflow.

e.g.

DO
$$
DECLARE
  sqlstring text;
BEGIN
  SELECT INTO sqlstring
   'UPDATE blah SET '
   || string_agg(format('%I = %I + INTERVAL ''1'' HOUR', column_name, column_name), ', ') 
   || ' WHERE true'
  FROM information_schema.columns
  WHERE table_name = 'blah'
    AND table_schema = 'public';

  EXECUTE sqlstring;
END;
$$;