CODEWITHSUNDEEP
sqlpostgresql
user8545255
user8545255

Reputation: 849

type cast in Case statement

Segment

   1
   2
   3
   4 
  NUll
   5

I want to impute 'Other' if the Segment value is null

expected output

  Segment

   1
   2
   3
   4 
  Other
   5

i have tried 

 select 
 case when segment is null then 'Other' else segment end as segment 
 from table;

It says invalid input syntax for type "numeric":Other

Upvotes: 1

Views: 2434

Answers (2)

chinh
chinh

Reputation: 1

select 
 case when CAST(segment AS CHAR) IS NULL then 'Other' else CAST(segment AS CHAR)  end as segment 
 from table

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

The case expression returns a single type. The problem is that segment is a number, but 'Other' is a string. The expression has to make a choice, and it chooses the numeric type (following standard SQL rules).

This is simple to fix. Just cast segment:

select (case when segment is null then 'Other' else segment::text end) as segment 
from table;

It would be more natural to write this query using coalesce():

select coalesce(segment::text, 'Other') as segment
from table;

Upvotes: 2

Related Questions