Using the CASE Statement in PostgreSQL for Conditional Queries

The CASE expression in PostgreSQL helps apply logic within SQL queries by returning values based on conditional evaluation. It’s particularly valuable when classifying data dynamically—such as assigning grades or setting labels according to thresholds.

This guide shows how to leverage the CASE statement in PostgreSQL to categorize data from a database. You’ll create a test setup with student and score tables and apply CASE logic to compute grades based on score intervals.

Requirements

Before proceeding, ensure the following setup:

• A running PostgreSQL instance with connection credentials.
• A Linux machine to act as your client.
• SSH access to the server.
• A sudo-enabled user (non-root).
• The psql client installed.

How PostgreSQL CASE Works

The CASE keyword in PostgreSQL is a logical operator used inside SQL statements. It checks multiple conditions in sequence and yields a result based on the first matching condition. This allows flexible data manipulation directly within your queries.

A CASE expression typically contains the following parts:

• CASE: Initiates the logic block.
• WHEN: Declares a condition to check.
• THEN: Defines the output for a true condition.
• END: Closes the expression.

Example CASE syntax within a SELECT statement:

SELECT 
    column1,
    column2,
    CASE 
        WHEN condition1 THEN 'Value A'
        WHEN condition2 THEN 'Value B'
        ELSE 'Default Value'
    END AS result_column
FROM your_table;

Valid conditional operators include:

• =: Equals
• <: Less than
• <=: Less than or equal to
• >: Greater than
• >=: Greater than or equal to

Setting Up the Example Database

To illustrate the CASE feature, begin by creating a simple schema with two tables: students and marks.

Log Into the PostgreSQL Server

Connect via the psql client using your credentials:

$ psql -h <host> -p <port> -U <username> defaultdb

Create the school Database

defaultdb=> CREATE DATABASE school;

Connect to the New Database

defaultdb=> \c school;

Define the students Table

Set up a table to store basic student info:

school=> CREATE TABLE students (
            student_id SERIAL PRIMARY KEY,
            first_name VARCHAR(50),
            last_name VARCHAR(50)
        );

Add Sample Student Data

school=> INSERT INTO students (first_name, last_name) VALUES
          ('JOHN', 'DOE'),
          ('JANE', 'SMITH'),
          ('PETER', 'HENRY'),
          ('MARY', 'ANN'),
          ('JESSICA', 'WILLIAMS'),
          ('BOB', 'JAMES'),
          ('ESTHER', 'CHLOE');

Verify Student Records

school=> SELECT student_id, first_name, last_name FROM students;

Expected output:

 student_id | first_name | last_name
------------+------------+-----------
        1   | JOHN       | DOE
        2   | JANE       | SMITH
        3   | PETER      | HENRY
        4   | MARY       | ANN
        5   | JESSICA    | WILLIAMS
        6   | BOB        | JAMES
        7   | ESTHER     | CHLOE
(7 rows)

Create the marks Table

Define a table to hold test scores linked to students.

school=> CREATE TABLE marks (
            score_id SERIAL PRIMARY KEY,
            student_id INT,
            score INT
        );

Insert Sample Score Data

school=> INSERT INTO marks (student_id, score) VALUES
          (1, 85),
          (2, 39),
          (3, 70),
          (4, 55),
          (5, 0),
          (6, 41),
          (7, 32);

Check Score Records

school=> SELECT score_id, student_id, score FROM marks;

Expected output:

 score_id | student_id | score
----------+------------+-------
        1 |          1 |    85
        2 |          2 |    39
        3 |          3 |    70
        4 |          4 |    55
        5 |          5 |     0
        6 |          6 |    41
        7 |          7 |    32
(7 rows)

Apply the CASE Expression to Assign Grades

The CASE expression can dynamically determine each student’s grade based on their score. By joining the students and marks tables, you can produce a report that includes the student’s name, their score, and a calculated grade.

Execute the following SQL command to achieve this:

school=> SELECT
             students.student_id,
             students.first_name,
             students.last_name,
             marks.score,
             CASE
                 WHEN marks.score >= 75 THEN 'Distinction'
                 WHEN marks.score >= 70 THEN 'Credit'
                 WHEN marks.score >= 40 THEN 'Pass'
                 ELSE 'Fail'
             END AS grade
         FROM marks
         LEFT JOIN students ON marks.student_id = students.student_id;

This SQL query uses a LEFT JOIN to connect each record in the marks table with its matching entry in the students table. The CASE logic reviews the score field and assigns grades based on these rules:

• Distinction for scores of 75 or more
• Credit for scores ranging from 70 to 74
• Pass for scores between 40 and 69
• Fail for scores below 40

Expected result:

 student_id | first_name | last_name | score |    grade
------------+------------+-----------+-------+-------------
          1 | JOHN       | DOE       |    85 | Distinction
          2 | JANE       | SMITH     |    39 | Fail
          3 | PETER      | HENRY     |    70 | Credit
          4 | MARY       | ANN       |    55 | Pass
          5 | JESSICA    | WILLIAMS  |     0 | Fail
          6 | BOB        | JAMES     |    41 | Pass
          7 | ESTHER     | CHLOE     |    32 | Fail
(7 rows)

This use case highlights how CASE brings clear and structured conditional behavior to SQL queries. It improves both readability and maintainability when applying rules directly within your SELECT logic.

Final Thoughts

In this walkthrough, you set up a sample PostgreSQL environment and implemented the CASE statement to categorize student scores. This technique simplifies SQL for tasks such as grading and decision-based transformations. For even more complex requirements, consider alternatives like mapping tables, procedural logic using IF, or intelligent solutions using technologies like pgvector for semantic filtering. To go deeper, refer to PostgreSQL’s official documentation on conditional expressions.