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

• Related Questions & Answers
• MySQL ORDER BY with CASE WHEN
• Alternative to MySQL CASE WHEN in MySQL
• Please explain what happens when PHP switch case executes case 0?
• Implement MySQL CASE statement with WHEN clause
• Perform count with CASE WHEN statement in MySQL?
• Aliasing in PostgreSQL?
• MySQL Mass Update with CASE WHEN/ THEN/ ELSE?
• Expression in CASE WHEN Clause doesn't work in MySQL query?
• Is there a case when finally block does not execute in Java?
• MySQL CASE WHEN with SELECT to display odd and even ids?
• Difference Between MySQL and PostgreSQL
• Get boolean result whether table exists or not using CASE WHEN in MySQL
• How can we sort a query using ORDER BY CASE WHEN REGEXP?
• CASE WHEN column1 IS NULL THEN NULL ELSE column2 END with MySQL
• How to create a table in PostgreSQL?