How to replace nulls with zeros in postgresql crosstabs

Question

I've a product table with product_id and 100+ attributes. The product_id is text whereas the attribute columns are integer, i.e. 1 if the attribute exists. When the Postgresql crosstab is run, non-matching atrributes return null values. How do I replace nulls with zeros instead.

SELECT ct.* INTO ct3 FROM crosstab( 'SELECT account_number, attr_name, sub FROM products ORDER BY 1,2', 'SELECT DISTINCT attr_name FROM attr_names ORDER BY 1') AS ct( account_number text, Attr1 integer, Attr2 integer, Attr3 integer, Attr4 integer, ... ) 

Replace this result:

account_number  Attr1   Attr2   Attr3   Attr4 1.00000001  1   null    null    null 1.00000002      null    null    1   null 1.00000003  null    null    1   null 1.00000004  1   null    null    null 1.00000005  1   null    null    null 1.00000006  null    null    null    1 1.00000007  1   null    null    null 

with this below:

account_number  Attr1   Attr2   Attr3   Attr4 1.00000001  1   0   0   0 1.00000002  0   0   1   0 1.00000003  0   0   1   0 1.00000004  1   0   0   0 1.00000005  1   0   0   0 1.00000006  0   0   0   1 1.00000007  1   0   0   0 

A workaround would be to do a select account_number, coalesce(Attr1,0)... on the result. But typing out coalesce for each of the 100+ columns is rather unyieldly. Is there a way to handle this using crosstab? Thanks

Answer 1

You can use coalesce:

select account_number,        coalesce(Attr1, 0) as Attr1,        coalesce(Attr2, 0) as Attr2,        etc