Set default value for all columns of a table

I have multiple tables with a lot of columns. Some columns contain Null values. Now, i want to set them to a default value (empty string or 0).

I found two two pieces of SQL that probably cover what i need but i can’t put them together.

select column_name,data_type 
from information_schema.columns 
where table_name = 'tablename';

I can use the above code to get all columns and their data type and the snippet below to update Null values in columns.

Update tablename Set col1 = ''
Where col1 Is Null;

How can i put these two together? Sorry, if this is a basic question but i couldn’t find a solution for this…

Answer

Surely, this is not basic question, and solution is little bit hard (not for experts). You have to generate SQL statement from system data stored in table pg_attribute and pg_attrdef.

You can run a following query. The result is text of UPDATE command, that does what you want:

select 'update foo set ' || 
       string_agg(attname || 
                  ' = coalesce(' || attname || ', '|| 
                               (select pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) 
                                  from pg_catalog.pg_attrdef d 
                                 where d.adrelid = a.attrelid 
                                   and d.adnum = a.attnum 
                                   and a.atthasdef )  || ')', ', ') 
 from pg_attribute a 
where attrelid = 'foo'::regclass and attnum > 0;

Example:

create table foo(a int default 0, b varchar default '');

run query and you get:

┌───────────────────────────────────────────────────────────────────────────┐
│                                 ?column?                                  │
╞═══════════════════════════════════════════════════════════════════════════╡
│ update foo set a = coalesce(a, 0), b = coalesce(b, ''::character varying) │
└───────────────────────────────────────────────────────────────────────────┘
(1 row)

Now you can run:

update foo set a = coalesce(a, 0), b = coalesce(b, ''::character varying)