How to Implement Case Statement Using Vultr Managed Database for PostgreSQL

by cobra_admin
March 7, 2024
66

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:

Deploy a Vultr Managed Database for PostgreSQL
Deploy a Vultr Linux server to use the management system
Access the server terminal using SSH
Create a non-root sudo user and switch to the account
Depending on your Linux distribution, install the PostgreSQL psql client tool. For example, on Ubuntu, run the following command:CONSOLECopy$ 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:

SQLCopySELECT sample_column_1, sample_column_2, sample_column_n, CASEWHEN sample_condition_1 THEN ‘sample_value_1’ WHEN sample_condition_2 THEN ‘sample_value_2’ WHEN sample_condition_n THEN ‘sample_value_n’ ENDAS 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

Log in to your Vultr Managed Database for PostgreSQL. Replace host.vultrdb.com, 16751 and vultradmin with your actual database detailsCONSOLECopy$ 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.
Create a sample school databaseSQLCopydefaultdb=> CREATE DATABASE school; Output:CREATE DATABASE
Switch to the new school database.SQLCopydefaultdb=> \c school; Output:You are now connected to database "school" as user "vultradmin".
Create a new students table with student_id,first_name and last_name columns.SQLCopyschool=> 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.
Insert sample data to the students table.SQLCopyschool=> 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’);
View the students table data to verify the new values.SQLCopyschool=> 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)
Create a new marks table with score_id, student_id, and score columns.SQLCopyschool=> 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.
Insert sample data to the marks table.SQLCopyschool=> 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.
View the marks table data to verify the valuesSQLCopyschool=> 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.

SQLCopyschool=> SELECT students.student_id, students.first_name, students.last_name, marks.score, ( CASEWHEN marks.score >= 75THEN ‘Distinction’ WHEN marks.score >= 70THEN ‘Credit’ WHEN marks.score >= 40THEN ‘Pass’ WHEN marks.score <= 39THEN ‘Fail’ END ) as grade FROM marks LEFTJOIN 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.

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…

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…

AI-powered Search with pgvector and Vultr Managed Database for PostgreSQL

How to Manage Users in Vultr Managed Databases for PostgreSQL

Cobra Softwares Blog