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

Bohemian · Accepted Answer · 2016-03-04 04:31:19Z

47

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.

edited Mar 4, 2016 at 4:31

answered Mar 4, 2016 at 4:25

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-11-25 22:24:55Z

12

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.

edited Nov 25, 2019 at 22:24

answered Mar 4, 2016 at 4:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Martin P. Over a year ago

Your example is missing a parenthesis after 'Missing'.

prettybug · Accepted Answer · 2024-01-06 20:59:42Z

5

I tried both answers and in my case both did not work. What ended up helping was this:

SELECT 
COALESCE(NULLIF(prodTypeCode,''), 'Missing') AS ProductCode,        
SUM(amount)
From sales s
group by prodTypeCode;

edited Jan 6, 2024 at 20:59

answered Nov 28, 2020 at 15:05

prettybug

1682 silver badges10 bronze badges

Collectives™ on Stack Overflow

Default value in select query for null values in postgres

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related