How to check for NULL in a CASE statement along with numeric Types in PostgreSQL DB?

Question

I want to run below statement but it is giving me the below error

select
case SEA.CONTENT_RESPONSE_STATE_ID
       when null then C.DISPLAY_NAME
       when 58 then 'Task Submission'
       when 59 then 'Sender Approved'
end AS ACTION
from table sea join table1 c

I am getting following error

ERROR: operator is not unique: numeric = boolean
Hint: Could not choose a best candidate operator. You might need to add explicit type casts.

I am not getting where I need to add cast and why?

I tried adding cast as below

 when CONTENT_RESPONSE_STATE_ID is null::numeric then C.DISPLAY_NAME

but the result is not correct in case of null although C.DISPLAY_NAME is not null but action is still getting populated as NULL

case when SEA.CONTENT_RESPONSE_STATE_ID is null then C.DISPLAY_NAME when SEA.CONTENT_RESPONSE_STATE_ID = 58 then ... end — jarlh
– jarlh, Commented Jun 30, 2021 at 10:23

user330315 · Accepted Answer · 2021-06-30 10:28:03Z

3

The CASE variant you want to use can only use equality expression. Nothing else. In your case you want to mix equality conditions with an IS NULL check. In order to be able to do that, you need to use the more verbose CASE variant:

 case 
   when CONTENT_RESPONSE_STATE_ID is null then display_name
   when CONTENT_RESPONSE_STATE_ID = 58 then 'Task Submission'       
   when CONTENT_RESPONSE_STATE_ID = 59 then 'Sender Approved'   
 end

answered Jun 30, 2021 at 10:28

user330315

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

1 Comment

Ankur Singh Over a year ago

I tried this but forgot to remove CONTENT_RESPONSE_STATE_ID after case Keyword so was getting the error. Thanks for help

Collectives™ on Stack Overflow

How to check for NULL in a CASE statement along with numeric Types in PostgreSQL DB?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related