Update multiple columns starting with a specific row

Question

I am trying to update a bunch of columns in a database for function testing purposes. I have a table built with hibernation, so all columns created for an inline object start with the same name. That is, contact_info_address_street1 , contact_info_address_street2 , etc.

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

 UPDATE table SET contact_info_address_* = null; 

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

Answer 1

There is no convenient shortcut. If you need to do a lot of things, you can 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(); 

This is not very nice. A better function would be one that takes the tablename and prefix prefix as args. What I will leave as an exercise for readers :)

Answer 2

For this you need dynamic SQL. Therefore, you should be prepared to deal with a possible SQL injection.

Main request

The basic request for creating the required DML command might look like this:

 SELECT format('UPDATE tbl SET (%s) = (%s)' ,string_agg (quote_ident(attname), ', ') ,string_agg ('NULL', ', ') ) FROM pg_attribute WHERE attrelid = 'tbl'::regclass AND NOT attisdropped AND attnum > 0 AND attname ~~ 'foo_%'; 

Return:

 UPDATE tbl SET (foo_a, foo_b, foo_c) = (NULL, NULL, NULL); 

• I use UPDATE " column-column syntax " to shorten the code and simplify the task.

• I request system catalogs instead of an information scheme , since the latter, being standardized and guaranteed for portability over major versions, is also known as slow and sometimes cumbersome. There are pros and cons, we discussed this several times here on SO. Search for keywords for more information.

• quote_ident() for column names prevents SQL injection and is also necessary for any non-standard column names.

• You neglected to mention your version of Postgres. The aggregate function string_agg() requires 9.0 +.

Full automation with PL / pgSQL function

 CREATE OR REPLACE FUNCTION f_update_cols(_tbl regclass, _col_pattern text , OUT row_ct int, OUT col_ct int) RETURNS record AS $func$ DECLARE _sql text; BEGIN SELECT format('UPDATE tbl SET (%s) = (%s)' ,string_agg (quote_ident(attname), ', ') ,string_agg ('NULL', ', ') ) ,count(*)::int INTO _sql, col_ct FROM pg_attribute WHERE attrelid = _tbl AND NOT attisdropped -- no dropped columns AND attnum > 0 -- no system columns AND attname ~~ _col_pattern; -- only columns matching pattern -- RAISE NOTICE '%', _sql; -- output generated SQL for debugging EXECUTE _sql; GET DIAGNOSTICS row_ct = ROW_COUNT; END $func$ LANGUAGE plpgsql; COMMENT ON FUNCTION f_update_cols(regclass, text) IS 'Updates all columns of table _tbl ($1) that match _col_pattern ($2) in a LIKE expression. Returns the count of columns (col_ct) and rows (row_ct) affected.'; 

Call:

 SELECT * FROM f_update_cols('myschema.tbl', 'foo%'); 

• To make the function more practical, it returns information as described in the comment. Read more about getting result status in plpgsql in the manual.

• I use the _sql variable to store the query string, so I can collect the number of columns found ( col_ct ) in the same query.

• The regclass object identifier type is the most efficient way to automatically exclude SQL injections (and disinfect non-standard names) for a table name. You can use table names with a table to avoid ambiguities. I would advise doing this if you have multiple circuits in your db! More details in this question:
  Table name as parameter of PostgreSQL function

→ SQLfiddle demo .