Change all zeros (if any) in all columns (in the table) to ... say 1

Question

I have a table with 18 columns (all ints) and 1040 rows. If any value is zero, I want to change it to 1. I use Postgresql. What is the best way to do this. I can't come up with a simple update statement ... and I'm new to DB.

Any pointers to what I should look at to find out how to achieve something similar ... (I would think something like a script if such a thing exists for postgresql .. I don't know)

thanks

Answer 1

How about this

UPDATE table SET columnA = 1 WHERE columnA = 0 

But you will need a query for each column, or

 UPDATE table SET columnA = CASE WHEN columnA = 0 THEN 1 ELSE columnA END, columnB = CASE WHEN columnB = 0 THEN 1 ELSE columnB END, ... 

Answer 2

Tool to replace setpoint with new value in all columns of comparable tables

I adapted the plpgsql function, which I use for a similar purpose:

 CREATE OR REPLACE FUNCTION f_update_all_cols(_sch text, _tbl text, _old int, _new int) RETURNS text AS $func$ DECLARE _type CONSTANT regtype[] := '{bigint,smallint,integer}'; _toid regclass; -- table oid _msg text := ''; -- report _ct integer; -- count of rows for report BEGIN -- Loop over tables FOR _toid IN SELECT c.oid FROM pg_class c JOIN pg_namespace nc ON nc.oid = c.relnamespace WHERE c.relkind = 'r' AND nc.nspname = _sch AND c.relname LIKE (_tbl || '%') ORDER BY c.relname LOOP EXECUTE ( -- RAISE NOTICE '%', ( SELECT format('UPDATE %s SET (%s) = (%s) WHERE $1 IN (%2$s)' , _toid , string_agg(quote_ident(attname), ', ' ORDER BY a.attnum) , string_agg(format('CASE WHEN %1$I = $1 THEN $2 ELSE %1$I END', attname), ', ') ) FROM pg_attribute a WHERE a.attrelid = _toid AND a.attnum >= 1 -- exclude neg. attnum - tableoid etc. AND NOT a.attisdropped -- exclude deleted columns AND a.atttypid = ANY(_type) GROUP BY _toid) USING _old, _new; -- ); GET DIAGNOSTICS _ct = ROW_COUNT; _msg := _msg || _ct || ' row(s) in: ' || _toid || E'\n'; END LOOP; RETURN _msg; END $func$ LANGUAGE plpgsql; COMMENT ON FUNCTION f_update_all_cols(text, text, int, int) IS $$ Convert 0 to 1 in all integer type columns. $1 .. _sch: schema $2 .. _tbl: table-pattern: left anchored search pattern; default "%" $3 .. _old: replace this ... $4 .. _new: ... with this) -- $$; 

Call:

 SELECT f_update_all_cols('myschema', '', 0, 1); -- all tables in schema SELECT f_update_all_cols('myschema', 'foo', 0, 1); -- tables starting with foo 

Look at all the columns of type integer and change the set value of _old to the set value of _new . If you want to include other data types, edit the _type variable accordingly.

Comment out the EXECUTE and USING lines and uncomment the RAISE NOTICE and closing parsers to check the generated code before executing.