CASE WHEN in PostgreSQL?

If you are a programmer, you may be very familiar with IF-ELSE statements. The equivalent in PostgreSQL is CASE WHEN.

Let’s understand with an example. If you have table marks containing percentage marks of a student, and you want to find out whether the students have passed or failed. An example table is given below.

Say the passing marks are 40. Now, if the student has scored above 40 marks, we want to print ‘PASS’ against that student’s name, otherwise ‘FAIL’. This is how you can do it −

SELECT name, CASE WHEN perc_marks >= 40 THEN 'PASS' ELSE 'FAIL' END
status from marks

The output will be −

Remember, the END at the end of the CASE WHEN expression is important. You can add multiple WHEN statements. Suppose you want to say that those who scored above 80 marks have the status ‘DISTINCTION’, between 40 and 80, they have status ‘PASS’ and below 40, they have the status ‘FAIL’, you can do that as follows −

SELECT name, CASE
WHEN perc_marks >= 80 THEN 'DISTINCTION'
WHEN perc_marks >= 40 and perc_marks < 80 THEN 'PASS'
ELSE 'FAIL' END status from marks

The output will be −

Yash Sanghvi

Published on 02-Feb-2021 12:55:14