How to Implement Case Statement Using Vultr Managed Database for PostgreSQL

Introduction

PostgreSQL is a high performance database server that allows you to store common data types and use different SQL tools to sort available data. These tools include the SQL CASE statement that evaluates a list of expressions and returns a value based on the possible results. For instance, in a school database, you can use the PostgreSQL CASE statement to grade student marks based on their scores.

For example:

scores    | grade         | 
----------+---------------+
  75 -100 | Distinction   |
  70 - 74 | Credit        |
  40 - 70 | Pass          |
  0  - 39 | Fail          |

Compared to the PostgreSQL IF statement, the CASE statement is more readable. You can use the CASE statement to construct cleaner and highly maintainable database code. This means other database users can understand the SQL code better and construct effective queries to get meaningful insights from the database.

This guide explains how you can implement the PostgreSQL CASE statement on a Vultr Managed Database for PostgreSQL to grade student marks.

Prerequisites

Before you begin:

1. Deploy a Vultr Managed Database for PostgreSQL

2. Deploy a Vultr Linux server to use the management system

3. Access the server terminal using SSH

4. Create a non-root sudo user and switch to the account

5. Depending on your Linux distribution, install the PostgreSQL psql client tool. For example, on Ubuntu, run the following command:

   console Copy

   $ sudo apt install -y postgresql-client
   

The PostgreSQL CASE Statement

The PostgreSQL CASE statement works together with a SELECT statement to query table data. However, the CASE statement must contain the following components:

• CASE: Start a PostgreSQL CASE statement.
• WHEN: Follows an expression that PostgreSQL evaluates to return a value.
• THEN: Defines a value that PostgreSQL returns when the given expression meets a condition.
• END: Terminates the CASE expression.

The PostgreSQL CASE statement uses the following query syntax:

SELECT 
    sample_column_1,
    sample_column_2,
    sample_column_n,
    CASE 
        WHEN sample_condition_1 THEN 'sample_value_1'
        WHEN sample_condition_2 THEN 'sample_value_2'
        WHEN sample_condition_n THEN 'sample_value_n'
    END AS sample_case_column
FROM SAMPLE_TABLE

In addition, the standard SQL operators help in the formation of most SQL CASE expressions, these include:

• =: Equal operator.
• <: Less than operator.
• <=: Less than or equal operator.
• >: Greater than operator.
• >=: Greater than or equal operator.

Create a Sample PostgreSQL Database

1. Log in to your Vultr Managed Database for PostgreSQL. Replace host.vultrdb.com, 16751 and vultradmin with your actual database details

   console Copy

   $ psql -h host.vultrdb.com -p 16751 -U vultradmin defaultdb
   

   When prompted, enter your correct Vultr Managed Database for PostgreSQL password to access the cluster.

2. Create a sample school database

   sql Copy

   defaultdb=> CREATE DATABASE school;
   

   Output:

   CREATE DATABASE

3. Switch to the new school database.

   sql Copy

   defaultdb=> \c school;
   

   Output:

   You are now connected to database "school" as user "vultradmin".

4. Create a new students table with student_id,first_name and last_name columns.

   sql Copy

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

   The above table query creates a new student_id column with a PRIMARY KEY that uniquely identifies each student in the table while the first_name and last_name columns store the students' names.

5. Insert sample data to the students table.

   sql Copy

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

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

   sql Copy

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

   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 new marks table with score_id, student_id, and score columns.

   sql Copy

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

   The above table query creates a score_id column with a PRIMARY KEY value. The student_id points to the students table you created earlier. The score column stores the actual points a student gets in the exam. In real-life examples, the score can relate to goals in a gaming application or steps in a fitness application.

8. Insert sample data to the marks table.

   sql Copy

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

   In the above queries, the student_id values are referenced from the students table while the score column accepts any values between 0 and 100.

9. View the marks table data to verify the values

   sql Copy

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

   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 PostgreSQL CASE Statement in a Query

Grade scores from the students table and join the marks table using the CASE statement to generate the necessary query results.

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'
                        WHEN marks.score <= 39 THEN 'Fail'
                    END
                ) as grade
            FROM marks
            LEFT JOIN students
            ON marks.student_id = students.student_id;

In the above query, you joined the marks and students tables to generate the following values:

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)

As displayed in the query output, the PostgreSQL CASE statement evaluates data from the students and marks tables to display the necessary data.

Conclusion

You have set up a sample PostgreSQL database and implemented the CASE SQL statement to generate user data from multiple tables. Apart from the CASE statement, PostgreSQL supports other data evaluation procedures such as the IF statement and lookup tables. For more infromation, visit the PostgreSQL conditional statements documentation.

Next Steps

To implement more solutions on your Vultr Managed Database for PostgreSQL, visit the following resources:

• How to Implement PostgreSQL Stored Procedures.
• AI-powered Search with pgvector and Vultr Managed Database for PostgreSQL.
• How to Import CSV Data to Vultr Managed Databases for PostgreSQL.