Default value in select query for null values in postgres

Question

I have a table with sales Id, product code and amount. Some places product code is null. I want to show Missing instead of null. Below is my table.

salesId     prodTypeCode    amount
1           123              150
2           123              200
3           234             3000
4           234              400
5           234              500
6           123              200
7           111              40
8           111              500
9                           1000
10          123              100

I want to display the total amount for every prodTypeCode with the option of If the prodTypeCode is null then Missing should be displayed.

select (CASE WHEN prodTypeCode IS NULL THEN
   'Missing'
    ELSE
    prodTypeCode
    END) as ProductCode, SUM(amount) From sales group by prodTypeCode

Above query giving error. Please suggest me to overcome this issue. I ahve created a SQLFIDDLE

Answer 1

Perhaps you have a type mismatch:

select coalesce(cast(prodTypeCode as varchar(255)), 'Missing') as ProductCode,     
       SUM(amount)
From sales s
group by prodTypeCode;

I prefer coalesce() to the case, simply because it is shorter.

Answer 2

The problem is a mismatch of datatypes; 'Missing' is text, but the product type code is numeric.

Cast the product type code to text so the two values are compatible:

select (CASE WHEN prodTypeCode IS NULL THEN
   'Missing'
    ELSE
    prodTypeCode::varchar(40)
    END) as ProductCode, SUM(amount) From sales group by prodTypeCode

See SQLFiddle.

Or, simpler:

select coalesce(prodTypeCode::varchar(40), 'Missing') ProductCode, SUM(amount)
from sales
group by prodTypeCode

See SQLFiddle.