In real life, we often make decisions based on conditions.
For example, a teacher checks a student’s marks:
In PostgreSQL, we can use the CASE expression to perform this type of conditional operation.
The CASE expression checks conditions and returns a value based on the first condition that is true.
It is similar to if-else statements in programming languages such as C++ and Python.
Syntax:
SELECT
column_name,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN condition3 THEN result3
ELSE default_result
END AS new_column_name
FROM table_name;
Important keywords:
CASE: Starts the conditional expression.WHEN: Specifies a condition.THEN: Specifies the result if the condition is true.ELSE: Specifies the default result if no condition is true.END: Ends the CASE expression.AS: Gives a name to the resulting column.Let’s create a table named students.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
marks INT
);
Insert sample records:
INSERT INTO students (student_id, student_name, marks)
VALUES
(1, 'Ali', 85),
(2, 'Sara', 72),
(3, 'Ahmed', 65),
(4, 'Usman', 45),
(5, 'Ayesha', 90);
Display all records:
SELECT * FROM students;
Let’s assign grades to students based on their marks.
SELECT
student_name,
marks,
CASE
WHEN marks >= 80 THEN 'A'
WHEN marks >= 70 THEN 'B'
WHEN marks >= 60 THEN 'C'
ELSE 'F'
END AS grade
FROM students;
Expected output:
| student_name | marks | grade |
|---|---|---|
| Ali | 85 | A |
| Sara | 72 | B |
| Ahmed | 65 | C |
| Usman | 45 | F |
| Ayesha | 90 | A |
Understand the query:
Important: PostgreSQL stops at the first matching WHEN condition.
The ELSE part is optional.
Consider this query:
SELECT
student_name,
marks,
CASE
WHEN marks >= 50 THEN 'Pass'
ELSE 'Fail'
END AS result
FROM students;
Expected output:
| student_name | marks | result |
|---|---|---|
| Ali | 85 | Pass |
| Sara | 72 | Pass |
| Ahmed | 65 | Pass |
| Usman | 45 | Fail |
| Ayesha | 90 | Pass |
If we omit ELSE, PostgreSQL returns NULL when none of the conditions is true.
We can also use CASE inside an UPDATE statement.
Suppose we want to assign performance categories to students.
ALTER TABLE students
ADD COLUMN performance VARCHAR(20);
Update the performance column:
UPDATE students
SET performance =
CASE
WHEN marks >= 80 THEN 'Excellent'
WHEN marks >= 60 THEN 'Good'
WHEN marks >= 50 THEN 'Average'
ELSE 'Poor'
END;
Check the updated records:
SELECT * FROM students;
Now each student has a performance category based on their marks.
We can use CASE to control the sorting order of records.
For example, display students in the following priority:
SELECT
student_name,
marks,
CASE
WHEN marks >= 50 THEN 1
ELSE 2
END AS priority
FROM students
ORDER BY priority;
Students with priority 1 appear first.
We can also sort without displaying the priority column:
SELECT student_name, marks
FROM students
ORDER BY
CASE
WHEN marks >= 50 THEN 1
ELSE 2
END;
CASE can also be used with aggregate functions.
For example, count how many students passed and failed.
SELECT
COUNT(
CASE WHEN marks >= 50 THEN 1 END
) AS passed_students,
COUNT(
CASE WHEN marks < 50 THEN 1 END
) AS failed_students
FROM students;
Expected output:
| passed_students | failed_students |
|---|---|
| 4 | 1 |
This is useful for preparing reports.
PostgreSQL supports two forms of CASE expressions.
A. Searched CASE
Checks conditions using comparison operators.
SELECT
student_name,
CASE
WHEN marks >= 80 THEN 'A'
WHEN marks >= 60 THEN 'B'
ELSE 'C'
END AS grade
FROM students;
B. Simple CASE
Compares one expression with different possible values.
SELECT
student_name,
CASE marks
WHEN 85 THEN 'Excellent'
WHEN 72 THEN 'Good'
WHEN 45 THEN 'Needs Improvement'
ELSE 'Other'
END AS performance
FROM students;
| Searched CASE | Simple CASE |
|---|---|
| Uses conditions after WHEN | Compares one expression with values |
Supports >=, <, =, etc. |
Uses equality comparisons |
| Suitable for ranges | Suitable for specific values |
| CASE Expression | IF-ELSE |
|---|---|
| Used in SQL queries | Used in programming logic |
| Returns a value | Controls program execution |
| Can be used in SELECT, UPDATE, ORDER BY | Used in procedural code |
| Evaluates conditions in order | Executes a matching code block |
Remember: CASE is an expression that returns a value; it is not a replacement for every programming control structure.
Use the students table created in this lesson.
Pass if marks are 50 or above; otherwise display Fail.grade to the students table and update it using CASE.| Keyword | Purpose |
|---|---|
| CASE | Starts conditional evaluation |
| WHEN | Defines a condition or comparison value |
| THEN | Returns a value when matched |
| ELSE | Returns a default value |
| END | Ends the CASE expression |
Remember: CASE allows us to make decisions directly inside SQL queries.
Views in PostgreSQL
You will learn how to create, use, update, and delete views, and how views simplify complex SQL queries.