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

Answer 2

if you can put those Attrs into a table like

attr
-----
Attr1

Attr2

Attr3

...

then you could automatically generate the repeating coalesce statement like

SELECT 'coalesce("' || attr || '", 0) "'|| attr ||'",' from table;

to save some typing.