How to update ALL columns of a table in PostgreSQL? Rather than doing them one column at a time.
Given this table:
Table1
Field1 | Field 2 | Field 3
123 | 987 | n/a
456 | n/a | 101
n/a | abcdef | n/a
The result should be:
Table1
Field1 | Field 2 | Field 3
123 | 987 | NA
456 | NA | 101
NA | abcdef | NA
I am looking for a single SQL query.
You can build the UPDATE command dynamically from the system catalogs in a DO statement or plpgsql function for that:
CREATE OR REPLACE FUNCTION f_global_replace(_tbl regclass
, _old text
, _new text
, OUT updated_rows int) AS
$func$
DECLARE
-- basic char types, possibly extend with citext, domains or custom types:
_typ CONSTANT regtype[] := '{text, bpchar, varchar}';
_sql text;
BEGIN
SELECT INTO _sql -- build command
format('UPDATE %s SET %s WHERE $1 IN (%s)'
, _tbl
, string_agg(format('%1$s = CASE WHEN %1$s = $1 THEN $2 ELSE %1$s END', col), ', ')
, string_agg(col, ','))
FROM (
SELECT quote_ident(attname) AS col -- escape names, prevent SQL injection!
FROM pg_attribute
WHERE attrelid = _tbl -- valid, visible, legal table name
AND attnum >= 1 -- exclude tableoid & friends
AND NOT attisdropped -- exclude dropped columns
AND atttypid = ANY(_typ) -- only character types
ORDER BY attnum
) sub;
-- RAISE NOTICE '%', _sql; -- debug
IF _sql IS NULL THEN
updated_rows := 0; -- nothing to update
ELSE
EXECUTE _sql USING _old, _new;
GET DIAGNOSTICS updated_rows = ROW_COUNT; -- Report number of affected rows
END IF;
END
$func$ LANGUAGE plpgsql;
This assembles and automatically executes a query of the form:
UPDATE table1
SET field1 = CASE WHEN field1 = $1 THEN $2 ELSE field1 END
, field2 = CASE WHEN field2 = $1 THEN $2 ELSE field2 END
, field3 = CASE WHEN field3 = $1 THEN $2 ELSE field3 END
WHERE $1 IN (field1,field2,field3);Careful! The function updates all character type columns. Be sure it works as expected. For safety I suggest to run it inside an explicit transaction and only commit after checking:
BEGIN;
SELECT * FROM f_global_replace('table1'::regclass, 'n/a', 'NA');
TABLE table1; -- all good?
COMMIT; -- then commit; else ROLLBACK;
Related answers with more information and links:
