Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sales_data (
  part_number text NOT NULL
, customer    text NOT NULL
, qty_2012_01 numeric
, qty_2012_02 numeric
, qty_2012_03 numeric
, CONSTRAINT sales_data_pk PRIMARY KEY (part_number, customer)
);

INSERT INTO sales_data
VALUES
  ('a', 'b', 11  , NULL, 33  )     -- some NULL values
, ('c', 'd', NULL, 22  , NULL)
, ('e', 'f', 44  , 55  , 66  )     -- no null value
;

SELECT ctid, * FROM sales_data;

CREATE TABLE



INSERT 0 3




ctid
part_number
customer
qty_2012_01
qty_2012_02
qty_2012_03


(0,1)
a
b
11
null
33

(0,2)
c
d
null
22
null

(0,3)
e
f
44
55
66



SELECT 3


DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          'UPDATE public.sales_data'
    || E'\nSET   ('      || string_agg(col, ', ')                          || ')'
    || E'\n    = ('      || string_agg('COALESCE(' || col || ', 0)', ', ') || ')'
    || E'\nWHERE  NOT (' || string_agg(col, ', ')                          || ') IS NOT NULL'
   FROM  (
      SELECT quote_ident(attname) AS col
      FROM   pg_attribute
      WHERE  attrelid = 'public.sales_data'::regclass
      AND    attnum >= 1                     -- exclude system columns
      AND    NOT attisdropped                -- exclude dropped columns
      AND    NOT attnotnull                  -- exclude columns defined NOT NULL
      AND    atttypid = 'numeric'::regtype   -- only numeric columns (?)
      ORDER  BY attnum
      ) sub;

   IF _sql IS NULL THEN
      RAISE WARNING 'No numeric column found in table "sales_data"!';
   ELSE
      -- RAISE NOTICE '%',  _sql;            -- test output for debugging
      EXECUTE _sql;                          -- payload
   END IF;
END
$do$;

-- Row without null values does not get updated: ctid unchanged!
SELECT ctid, * FROM sales_data;

DO




ctid
part_number
customer
qty_2012_01
qty_2012_02
qty_2012_03


(0,3)
e
f
44
55
66

(0,4)
a
b
11
0
33

(0,5)
c
d
0
22
0



SELECT 3


-- Demo for "NOT (ROW(x,y) IS NOT NULL)"

SELECT *
     , t IS NULL           AS fail1 
     , t IS NOT NULL       AS fail2
     , NOT (t IS NOT NULL) AS bingo
     , NOT ((c1,c2,c3) IS NOT NULL) AS bingo_faster
     , num_nulls(c1,c2,c3) > 0      AS bingo_slower
FROM (
   VALUES
     (null, 1, null)
   , (1, 1, 1)
   , (null, null, null)
   ) t (c1,c2,c3);


c1
c2
c3
fail1
fail2
bingo
bingo_faster
bingo_slower


null
1
null
f
f
t
t
t

1
1
1
f
t
f
f
f

null
null
null
t
f
t
t
t



SELECT 3
c1	c2	c3	fail1	fail2	bingo	bingo_faster	bingo_slower
null	1	null	f	f	t	t	t
1	1	1	f	t	f	f	f
null	null	null	t	f	t	t	t