Execute dynamic SQL query stored in text variable using Postrgres plpgsql [duplicate]

Question

In the Postgres table named my_table, I want to set all empty strings ('') across all variables to null. I have the following do-block which fails at the execute line.

I am quite new to plpgsql and do not understand why. How can I properly execute the command stored in q?

do language plpgsql
$$
DECLARE
  r record;
  q text;
BEGIN
   for r in
        select table_name, column_name
        from information_schema.columns t
        where t.table_name = 'my_table'
  loop
    q := format('UPDATE %s SET %s = NULL WHERE %s = '''';',
                    r.table_name, r.column_name, r.column_name);
    raise notice 'cleaning column: %', q;
    execute q;  -- this line fails
   end loop;
END;
$$;

PS. Any other hints for better code are also welcome :)

Answer 1

For a non-text column (say integer id) the query

UPDATE id SET id = NULL WHERE id = ''

will raise an error as you cannot compare integer id to text.

Use cast to text:

...
q := format('UPDATE %I SET %I = NULL WHERE %I::text = '''';',
            r.table_name, r.column_name, r.column_name);
...

As an alternative you can execute the update query only for text columns, e.g.:

...
    select table_name, column_name
    from information_schema.columns t
    where t.table_name = 'my_table'
    and data_type in ('text', 'character varying')
...