Postgres - convert null result column to zero in crosstab query

Question

I have a crosstab query that is working fine. It is based on customers and their total transactions, split by months of the year.

The only issue is that when there is no data for a column (ie no activity for a month) then I get a null value which I would like to convert to a zero.

I have tried coalesce on the 'amount' field, but that does not work.

If anyone has any pointers to help I would be very grateful.

The query is:

select *
from crosstab(
 $ct$
    SELECT  sa.id, 
            company.name, 
            to_char(sat.transaction_date, 'YYYY-MM') AS my,
            COALESCE(sat.amount,0) AS amnt
    FROM sales_account_transactions sat
    JOIN sales_account sa ON sa.id = sat.sales_account
    JOIN company ON sa.company = company.id
    WhERE sat.financial_company = 1
    AND sat.transaction_date BETWEEN '2018-01-01' AND '2018-03-31'
    AND sat.reversed_by = 0
    AND sat.original_id = 0
    GROUP BY sa.id, company.name, my, amnt
    ORDER BY company.name, to_char(sat.transaction_date, 'YYYY-MM');
$ct$,
$$VALUES
  ('2018-01'), ('2018-02'), ('2018-03')
$$
) 
as ct(id  int, name text,
"Jan 2018" int, "Feb 2018" int, "Mar 2018" int);

Answer 1

Instead of select *, use select coalesce("Jan 2018", 0) as "Jan 2018", ...