Postgres CASE WHEN IN query

Question

I've attempted the below query in postgres 11

CASE
           WHEN planning_status::varchar in (('Application Under Consideration'::varchar,'Appeal In Progress'::varchar))  then 'this_label'
           WHEN planning_status::varchar  = 'Approved' and  actual_completion_date is not null then 'that_label'
    ELSE 'reject_label'
    END

I can't get the query to run, initially getting error on mismatching operator to record type. I also attempted IN (VALUES()) method. The below works:

       CASE
          WHEN planning_status = 'Application Under Consideration'  then 'this_label'
          WHEN planning_status = 'Appeal In Progress'  then 'this_label'
          WHEN planning_status = 'Application Received'  then 'this_label'
          WHEN planning_status  = 'Approved' and  actual_completion_date is not null then 'that_label'
    ELSE 'reject_label'
    END

Is it possible to use the IN query within a CASE WHEN query with strings. The strings are categorical but not stored as such

Laurenz Albe · Accepted Answer · 2021-08-30 11:22:00Z

1

The problem are the double parentheses:

-- this doesn't work:
SELECT CASE WHEN 1 IN ((1, 2)) THEN 'works' ELSE 'weird' END;
ERROR:  operator does not exist: integer = record
LINE 1: SELECT CASE WHEN 1 IN ((1, 2)) THEN 'works' ELSE 'weird' END...
                           ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

-- this works:
SELECT CASE WHEN 1 IN (1, 2) THEN 'works' ELSE 'weird' END;

 case  
═══════
 works
(1 row)

The reason is that in the first statement, the inner parentheses are forming a composite type (record) with two elements, and PostgreSQL doesn't know how to compare that to the integer 1.

answered Aug 30, 2021 at 11:22

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2021-08-30 11:13:56Z

0

If the = version works, then this should work:

  (CASE WHEN planning_status IN ('Application Under Consideration', 'Appeal In Progress', 'Application Received')
        THEN 'this_label'
        WHEN planning_status  = 'Approved' and  actual_completion_date is not null 
        THEN 'that_label'
        ELSE 'reject_label'
   END)

There should be no need for an explicit type conversion. If this doesn't work, what is the type of planning_status?

answered Aug 30, 2021 at 11:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

mapping dom Over a year ago

Thankyou, putting CASE WHEN within () has resolved it

Gordon Linoff Over a year ago

@mappingdom . . . That should actually have nothing to do with the solution. That is just a habit I use to help my reading of expressions and to prevent other types of errors.

Collectives™ on Stack Overflow

Postgres CASE WHEN IN query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related