How to handle Null fields in Postgresql

Question

Hello, I need to know how to best use COALESCE to treat the null fields in my table. My problem is the following, I’m simulating 6 Stores and their respective cards, let’s assume that one or more stores did not sell product on the Amex card, when I run my query it returns me 'null'. And I needed to replace it with zero. With COALESCE is that possible? Following picture :

Image of the query:

Answer 1

The coalesce is a function that returns the value of the first parameter if it is not null. If null returns the value entered in the second parameter, according to documentation [1].

In the case of the informed query you first need to modify the fields by performing an explicit conversion to numeric and informing in the second parameter 0 where there is no flag value.

coalesce(cast(vb.bandeiras->>'AMEX' as numeric), 0) as amex

Follow full query below. I created a new column ELO that has no records in the tables to simulate the treatment of null values.

with vendas as(
    select 
        t.nome as store_nome,
        c.nome as card_nome,
        sum(s.valor) as total_sale
    from sale s 
        inner join store t 
            on (t.id = s.store_id_fk)
        inner join credit_card c 
            on (c.id = s.credit_card_fk)
    where 
        data between '2017-01-01' and '2017-01-31' 
    group by 
        t.nome,
        c.nome 
), vendas_por_loja as (
    select
        store_nome,
        sum(total_sale) as total
    from vendas
    group by store_nome
), vendas_por_bandeira as (
    select 
        store_nome,
        jsonb_object_agg(card_nome,total_sale) as bandeiras
    from
        vendas
    group by store_nome
)
select 
    vb.store_nome,
    coalesce(cast(vb.bandeiras->>'AMEX' as numeric), 0) as amex,
    coalesce(cast(vb.bandeiras->>'VISA'  as numeric), 0)as visa,
    coalesce(cast(vb.bandeiras->>'DINERS'  as numeric), 0)as diners,
    coalesce(cast(vb.bandeiras->>'MASTER' as numeric), 0) as master,
    coalesce(cast(vb.bandeiras->>'ELO' as numeric), 0) as elo,
    vl.total
from vendas_por_bandeira vb
    inner join vendas_por_loja vl
        on (vb.store_nome=vl.store_nome);

store_nome | amex | visa | diners | master | elo | total 
------------+------+------+--------+--------+-----+-------
Loja 02    |   60 |   60 |     60 |     60 |   0 |   240
Loja 01    |  150 |  100 |     80 |     50 |   0 |   380
(2 registros)

[1] https://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL