- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
name | perc_marks |
---|---|
Anil | 24 |
Joy | 65 |
Ron | 42 |
Reena | 87 |
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 −
name | status |
---|---|
Anil | FAIL |
Joy | PASS |
Ron | PASS |
Reena | PASS |
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 −
name | status |
---|---|
Anil | FAIL |
Joy | PASS |
Ron | PASS |
Reena | DISTINCTION |
- Related Articles
- Aliasing in PostgreSQL?
- Alternative to MySQL CASE WHEN in MySQL
- MySQL ORDER BY with CASE WHEN
- Perform count with CASE WHEN statement in MySQL?
- Please explain what happens when PHP switch case executes case 0?
- Implement MySQL CASE statement with WHEN clause
- MySQL Mass Update with CASE WHEN/ THEN/ ELSE?
- How to create a table in PostgreSQL?
- Difference Between MySQL and PostgreSQL
- How to Kill queries in pgAdmin in PostgreSQL?
- Expression in CASE WHEN Clause doesn't work in MySQL query?
- Python Getting started with psycopg2-PostgreSQL
- How to Query a DB in pgAdmin in PostgreSQL?
- Is there a case when finally block does not execute in Java?
- Simulating MySQL's ORDER BY FIELD() in PostgreSQL?
