How to replace nulls with zeros in postgresql crosstabs
41,335
You can use coalesce:
select account_number,
coalesce(Attr1, 0) as Attr1,
coalesce(Attr2, 0) as Attr2,
etc
Related videos on Youtube
Author by
Mike
Updated on July 09, 2022Comments
-
Mike almost 2 years
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