Postgres: COALESCE all columns

Question

I'm dealing with a table that has way too many columns for good design. (Close to 100). I can't change the schema.

I need to COALESCE each column to be 0 on NULL. Is there a way to do this without typing out all 100 columns by hand? (Perhaps using some type of data dictionary or meta information?)

Answer 1

You can create a Postgres function to achieve the desired output.

Sample table and data -

create table t(id serial, col1 int, col2 int, col3 int, col4 int, col5 int, col6 int, coln int); 
insert into t values ( 1, 1, 2, null,null,null,1),(1, null, null, null,2,null,6);

Following select statement will create dynamic select to get all tables with coalesce().

SELECT format('select %s from t', string_agg(format('coalesce(%s,0)', column_name), ',')) q
FROM information_schema.columns
WHERE table_schema = 'public'
    AND table_name = 't'

Result:

q                                                                                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------- 
select coalesce(id,0),coalesce(col1,0),coalesce(col2,0),coalesce(col3,0),coalesce(col4,0),coalesce(col5,0),coalesce(col6,0),coalesce(coln,0) from t 

(1 row(s) affected)

You can wrap this into a function

 create or replace function get_table_t ()
returns setof t as $$
declare qry text;
begin
    SELECT format('select %s from t', string_agg(format('coalesce(%s,0)', column_name), ','))
    into qry
    FROM information_schema.columns
    WHERE table_schema = 'public'
        AND table_name = 't'; 
    return query

    execute qry;
end;$$
LANGUAGE plpgsql VOLATILE

Usage: select * from get_table_t()

Output:

id col1 col2 col3 col4 col5 col6 coln 
-- ---- ---- ---- ---- ---- ---- ---- 
1  1    2    0    0    0    1    0    
1  0    0    0    2    0    6    0