How to replace zeros with zeros in postgresql crosstabs

Question

I have a product table with product_id and 100+ attributes. Product_id is the text, while the attribute columns are integer, i.e. 1 if the attribute exists. When a Postgresql crosstab is executed, non-retrieving attributes return null values. How to replace zeros 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 

in the following way:

 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 make select account_number, combine (Attr1,0) ... as a result. But typing coalesce for each of the 100+ columns is pretty opaque. Is there a way to handle this using a crosstab? Thanks

Answer 1

You can use coalesce:

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

Answer 2

if you can put these attrs in a table like

atr

Attr1

attr2

Attr3

...

then you can automatically generate a repeating coalesce statement like

SELECT 'coalesce("' || attr || '", 0) "'|| attr ||'",' from table; to save some typing.