CASE WHEN in PostgreSQL?

PostgreSQLDatabaseData Storage

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.

nameperc_marks
Anil24
Joy65
Ron42
Reena87

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 −

namestatus
AnilFAIL
JoyPASS
RonPASS
ReenaPASS

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 −

namestatus
AnilFAIL
JoyPASS
RonPASS
ReenaDISTINCTION


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