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?

  • 4
    You don't need to run multiple updates, you can update more than one column in a single update.
    – user1822
    Mar 30, 2015 at 10:22
  • I formulated my question bad. What I meant was of course not to have to repeat the pattern in the query (or just write multiple queries if you like).
    – nyde
    Mar 31, 2015 at 7:51

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

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.


  sqlstring text;
  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;
  • @a_horse_with_no_name Yes, rather. Mar 30, 2015 at 14:32
  • @a_horse_with_no_name and added example. BTW, in a totally unrelated post (just deleted by poster, hence mentioning here) you wrote that MySQL ignores foreign keys. Not true since 5.0, it's like saying MySQL doesn't support transactions, PostgreSQL requires manual VACUUM to keep working, or PostgreSQL doesn't support replication. Separately to that, using MyISAM should require starting MySQL with a "--eat-my-data-please-I-want-to-use-myisam" flag, but .... Mar 30, 2015 at 14:46
  • 3
    I wrote "MySQL ignores inline foreign key constraints" which is still true with 5.6: sqlfiddle.com/#!9/fc801/1
    – user1822
    Mar 30, 2015 at 14:52
  • @a_horse_with_no_name Ah. Important word there. Gotcha. Also ugh. Mar 30, 2015 at 15:04
  • 1
    @a_horse_with_no_name, CraigRinger: parses for correctness and then ignores (doesn't even check if the referenced table or column exist or if the number of columns or types match): sqlfiddle-ulala Mar 30, 2015 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.