sql - Set empty strings ('') to NULL in the whole database -

in database many text columns values empty strings (''). empty strings need set null. not know exact schemas, tables , columns in database or rather want write general solution can reused.

how write query / function find text columns in tables in schemas , update columns empty strings ('') null?

the efficient way achieve this:

• run single update per table.
• only update nullable columns (not defined not null) actual empty string.
• only update rows actual empty string.
• leave other values unchanged.

this related answer has plpgsql function builds , runs update command using system catalog pg_attribute automatically , safely given table:

• replace empty strings null values

using function f_empty2null() answer, can loop through selected tables this:

do $do$ declare    _tbl regclass; begin    _tbl in       select c.oid::regclass         pg_class c       join   pg_namespace n on n.oid = c.relnamespace        c.relkind = 'r'            -- regular tables       ,    n.nspname not 'pg_%'  -- exclude system schemas    loop       raise notice $$perform f_empty2null('%');$$, _tbl;       -- perform f_empty2null(_tbl);  -- uncomment prime bomb    end loop; end $do$; 

careful! updates empty strings in columns of user tables in db. sure that's want or might nuke database.

you need update privileges on selected tables, of course.

as child safety device commented payload.

you may have noted use system catalogs directly, not information schema (which work, too). this:

• how check if table exists in given schema
• query return output column names , data types of query, table or view

for repeated use

here integrated solution repeated use. without safety devices:

create or replace function f_all_empty2null(out _tables int, out _rows int) $func$ declare    _typ constant regtype[] := '{text, bpchar, varchar, \"char\"}';    _sql text;    _row_ct int; begin    _tables := 0;  _rows := 0;    _sql in       select format('update %s set %s %s'                   , t.tbl                   , string_agg(format($$%1$s = nullif(%1$s, '')$$, t.col), ', ')                   , string_agg(t.col || $$ = ''$$, ' or '))        (          select c.oid::regclass tbl, quote_ident(attname) col            pg_namespace n          join   pg_class     c on c.relnamespace = n.oid          join   pg_attribute on a.attrelid = c.oid           n.nspname not 'pg_%'      -- exclude system schemas          ,    c.relkind = 'r'                -- regular tables          ,    a.attnum >= 1                  -- exclude tableoid & friends          ,    not a.attisdropped             -- exclude dropped columns          ,    not a.attnotnull               -- exclude columns defined not null!          ,    a.atttypid = any(_typ)         -- character types          order  a.attnum          ) t       group  t.tbl    loop       execute _sql;       diagnostics _row_ct = row_count;     -- report nr. of affected rows       _tables := _tables + 1;       _rows := _rows + _row_ct;    end loop; end $func$  language plpgsql; 

call:

select * pg_temp.f_all_empty2null(); 

returns:

 _tables | _rows ---------+---------  23      | 123456 

note how escaped both table , columns names properly!

c.oid::regclass tbl, quote_ident(attname)  col 

consider:

• table name postgresql function parameter

careful! same warning above.
consider basic explanation in answer linked above:

• replace empty strings null values