Update multiple columns that start with a specific string

Question

I am trying to update a bunch of columns in a DB for testing purposes of a feature. I have a table that is built with hibernate so all of the columns that are created for an embedded entity begin with the same name. I.e. contact_info_address_street1, contact_info_address_street2, etc.

I am trying to figure out if there is a way to do something to the affect of:

UPDATE table SET contact_info_address_* = null;

If not, I know I can do it the long way, just looking for a way to help myself out in the future if I need to do this all over again for a different set of columns.

Answer 1

There's no handy shortcut sorry. If you have to do this kind of thing a lot, you could create a function to dynamically execute sql and achieve your goal.

CREATE OR REPLACE FUNCTION reset_cols() RETURNS boolean AS $$ BEGIN 
    EXECUTE (select 'UPDATE table SET ' 
                  || array_to_string(array(
                              select column_name::text 
                              from information_schema.columns 
                              where table_name = 'table' 
                              and column_name::text like 'contact_info_address_%'
                     ),' = NULL,') 
                  || ' = NULL'); 
    RETURN true; 
 END; $$ LANGUAGE plpgsql;

-- run the function
SELECT reset_cols();

It's not very nice though. A better function would be one that accepts the tablename and column prefix as args. Which I'll leave as an exercise for the readers :)