Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE OR REPLACE FUNCTION f_empty_text_to_null(_tbl regclass, OUT updated_rows int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _typ  CONSTANT regtype[] := '{text, bpchar, varchar}';  -- ARRAY of all basic character types
   _sql  text;
BEGIN
   SELECT INTO _sql                       -- build SQL command
          'UPDATE ' || _tbl
          || E'\nSET    ' || string_agg(format('%1$s = NULLIF(%1$s, '''')', col), E'\n      ,')
          || E'\nWHERE  ' || string_agg(col || ' = ''''', ' OR ')
   FROM  (
      SELECT quote_ident(attname) AS col
      FROM   pg_attribute
      WHERE  attrelid = _tbl              -- valid, visible, legal table name 
      AND    attnum >= 1                  -- exclude tableoid & friends
      AND    NOT attisdropped             -- exclude dropped columns
      AND    NOT attnotnull               -- exclude columns defined NOT NULL!
      AND    atttypid = ANY(_typ)         -- only character types
      ORDER  BY attnum
      ) sub;

   -- RAISE NOTICE '%', _sql;  -- test?

   -- Execute
   IF _sql IS NULL THEN
      updated_rows := 0;                        -- nothing to update
   ELSE
      EXECUTE _sql;
      GET DIAGNOSTICS updated_rows = ROW_COUNT; -- Report number of affected rows
   END IF;
END
$func$;

-- Test tables --

-- NO string-type columns -> no UPDATE
CREATE TABLE t0(id int, d date);
INSERT INTO t0 VALUES (1, NULL), (2, now()::date);

-- 1 string-type column
CREATE TABLE t1(id int, txt text);
INSERT INTO t1 VALUES (1,''), (2, null), (3, 'foo');

 -- 4 string-type columns, last one defined NOT NULL!
CREATE TABLE t4(id int, txt1 text, txt2 varchar, txt3 varchar(10), txt4 char(17) NOT NULL);
INSERT INTO t4 VALUES (1,'','','',''), (2, null,'foo','','bar'), (3, 'foo', '', 'bar','');
2 rows affected

3 rows affected

3 rows affected

TABLE t0 ORDER BY id;

id
d

1
null

2
2020-12-02

SELECT f_empty_text_to_null('t0');

f_empty_text_to_null

0

TABLE t0 ORDER BY id;

id
d

1
null

2
2020-12-02

TABLE t1 ORDER BY id;

id
txt

1

2
null

3
foo

SELECT f_empty_text_to_null('t1');

f_empty_text_to_null

1

TABLE t1 ORDER BY id;

id
txt

1
null

2
null

3
foo

-- Note: last column defined NOT NULL
TABLE t4 ORDER BY id;

id
txt1
txt2
txt3
txt4

1

2
null
foo

bar

3
foo

bar
                 
SELECT f_empty_text_to_null('t4');

f_empty_text_to_null

3

TABLE t4 ORDER BY id;

id
txt1
txt2
txt3
txt4

1
null
null
null
                 
2
null
foo
null
bar

3
foo
null
bar

id	d
1	null
2	2020-12-02

id	d
1	null
2	2020-12-02

id	txt
1	null
2	null
3	foo