Changing all zeros (if any) across all columns (in a 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 am using Postgresql. What is the best way to do this. I cannot come up with a simple update statement... and I am new to DBs.

Any pointers on what I should look at to learn how to achieve something like this... (I would think some sort of script, if such thing exists for postgresql.. I don't know)

Thanks

update table FOO SET column_1=1 where column_1=0 and do this 18 times? — nulltorpedo, Mar 21 '12 at 23:14
Since the table is pretty small, you could also grab the data with a scripting language (Perl, Python, etc), do the variable substitution row by row as the data is being read in, truncate the table, and then insert the updated data (you'd probably want to put the last two steps into a transaction). — , Mar 22 '12 at 00:51

Ben · Accepted Answer · 2012-03-21T23:28:25.187

2

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

edited Mar 21 '12 at 23:28

answered Mar 21 '12 at 23:15

Ben

539
3
7

I was looking for editing all 18 at the same time (for learning purpose). That would work with 18 separate queries – nulltorpedo Mar 21 '12 at 23:17
Actually the above will work with one query, it will just be one big tedious query. – David Faber Mar 21 '12 at 23:25
@David. Correct, I commented before he updated with second case – nulltorpedo Mar 21 '12 at 23:27

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

Tool to replace given value with new value in all columns of matching tables

I adapted a plpgsql function I have in 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

Looks at all integer type columns and changes the given _old value into the given _new value. If you want to include other data types, edit the variable _type accordingly.

Comment the EXECUTE and the USING line and uncomment RAISE NOTICE and the closing parens to inspect the generated code before you execute.

Changing all zeros (if any) across all columns (in a table) to... say 1

2 Answers2

Tool to replace given value with new value in all columns of matching tables

Linked