How to Use the Case Statement in PostgreSQL

The CASE statement in PostgreSQL allows you to evaluate conditions and return values based on those conditions. It’s useful when you want to add conditional logic to queries. For example, grading student scores or classifying records based on thresholds.

This article demonstrates how to use the PostgreSQL CASE statement to generate categorized results from database tables. You’ll create a sample schema with student records and marks, then use a CASE statement to assign grades based on score ranges.

Prerequisites

Before you begin:

• Provision a managed PostgreSQL database and note the connection credentials.
• Have access to a Linux instance to use as your database client.
• Connect to your server via SSH.
• Create a non-root user with sudo privileges.
• Make sure the PostgreSQL psql client is installed on your server.

Understanding the CASE Statement in PostgreSQL

The CASE statement in PostgreSQL is a conditional expression used to add logic within a SQL query. It evaluates conditions sequentially and returns a result based on the first condition that evaluates to true. This is especially useful when transforming or classifying data during query execution.

A typical CASE block contains the following components:

• CASE: Begins the conditional logic block.
• WHEN: Defines a condition to evaluate.
• THEN: Specifies the value to return if the corresponding WHEN condition is true.
• END: Closes the CASE expression.

Here is a basic example of the CASE syntax within a SELECT query:

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

You can use any standard SQL comparison operators in your conditions, including:

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

Create a Sample Database and Tables

To demonstrate the CASE statement in action, start by creating a sample database with two tables: students and marks.

1. Log in to your PostgreSQL server using the psql client. Replace the hostname, port, and username with your actual credentials.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

2. Create a new database named school.

   SQL

   Copy

   defaultdb=> CREATE DATABASE school;

   Explain Code

   Powered by Vultr AgentBeta

3. Connect to the newly created school database.

   SQL

   Copy

   defaultdb=> \c school;

   Explain Code

   Powered by Vultr AgentBeta

4. Create a students table with columns for the student ID, first name, and last name.

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

5. Insert sample student records into the students table.

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

6. View the students table data to verify the new values.

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   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)

7. Create a marks table to store student scores.

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

8. Insert sample scores into the marks table.

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

9. Retrieve data from the marks table.

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   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)

Implement the CASE Statement to Grade Students

You can use the CASE expression to evaluate each student's score and assign a grade dynamically. By joining the students and marks tables, you can generate a report that includes names, scores, and computed grade categories.

Run the following SQL query:

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 query performs a LEFT JOIN to match each score with the corresponding student record. The CASE block evaluates the score column and returns:

• Distinction for scores 75 and above
• Credit for scores between 70 and 74
• Pass for scores between 40 and 69
• Fail for scores below 40

Output.

 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 example demonstrates how CASE enables clear, conditional logic directly within a SELECT query, making your SQL both powerful and easy to maintain.

Conclusion

In this article, you created a sample PostgreSQL database and used the CASE statement to classify student scores with readable and maintainable SQL logic. This conditional technique simplifies query structure, especially for grading or rule-based categorization. For more advanced logic, you can explore PostgreSQL features like lookup tables, IF statements, or AI-powered solutions such as semantic search with pgvector. To learn more, visit the official PostgreSQL conditional expressions documentation.