title: How to replace nulls with zeros in postgresql crosstabs
body: <p>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.</p>
  
  <pre><code>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,
  ...
  )
  </code></pre>
  
  <p>Replace this result:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>with this below:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>