Working with Postgres Case Statement: A Simplified Guide

Do you wish to understand what Postgres Case statement is? Do you want to know how the Postgres Case statement works along with the proper syntax? If yes, then you’ve come to the right place.

This article will provide you with a simplified explanation of what a Relational Database Management System (RDBMS) is, why PostgreSQL is becoming the preferred RDBMS for most organizations, what a Postgres Case statement is, and how you can use it for your business and data requirements.

What is Relational Database Management System?

A Relational Database Management System (RDMS) can be defined as a set of programs that allow you to interact with Relational Databases and make changes to them as required. 

A Relational Database refers to a database that stores data in a Structured format i.e., in the form of rows and columns. Each table in the Relational Database is referred to as a Relation. Each row is referred to as a Record or Tuple, and each column is referred to as an Attribute.

Data in a Relational Database is queried using Structured Query Language (SQL). Each record in a table can be uniquely identified using an Attribute that acts as a Primary Key and a relationship can be established between two different tables using a Foreign Key. This type of relationship between different tables allows queries to be run across multiple tables at once.

One of the most significant advantages of using RDBMS is its implementation of the ACID properties, a description of which is as follows:

• Atomicity: This signifies that all updates being made to the database are performed in a single operation. This means that either all updates in that operation are performed or none of them are.
• Consistency: This signifies that all the data in the database is in a consistent state before and after an operation is done.
• Isolation: This signifies that multiple operations can be performed on the database independently of each other.
• Durability: This signifies that changes made to a database persist even if a system failure occurs.

Some of the most widely used Relational Database Management Systems are Oracle Database, MySQL, PostgreSQL, Microsoft SQL Server, and IBM DB2.

What is PostgreSQL?

PostgreSQL is an Open-Source and one of the most powerful Relational Database Management Systems. It incorporates SQL and also adds a set of new features that allow PostgreSQL to not only be used for Transactional Databases but also with and as a Data Warehouse for analytical purposes. 

One of the most significant advantages of using PostgreSQL and why it’s becoming the preferred choice for most businesses using Relational Databases is its ability to support the Object Relational Model which allows users to define custom data types depending on the use case in their application.

What is Postgres Case Statement?

The Postgres Case statement can be seen as the same as IF/ELSE statements in most programming languages. It allows you to add conditional logic to your query that can help you automate some operations based on a particular condition. The syntax for the Postgres Case statement is as follows:

CASE 
      WHEN condition_1  THEN result_1
      WHEN condition_2  THEN result_2
      [WHEN ...]
      [ELSE else_result]
END

The conditions here, i.e., condition_1, condition_2, etc, should be boolean expressions, and hence their evaluation should only return a TRUE or FALSE value. The results, i.e., result_1, result_2, etc., signify the possible values that can be returned. The result value corresponding to a TRUE condition is what is returned by the Postgres Case statement.

PostgreSQL Comparison Operators

All comparison operators in PostgreSQL can be used in the condition of the Case statement. The comparison operators in PostgreSQL are as follows:

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

Two Forms of Postgres Case Statement

The two forms of Postgres Case statements are as follows:

• Simple Case Statement
• Searched Case Statement

1) Simple Case Statement

The syntax for a Simple Case statement is as follows:

CASE search-expression
   WHEN expression_1 [, expression_2, ...] THEN
      when-statements
  [ ... ]
  [ELSE
      else-statements ]
END CASE;

In this syntax, the “search-expression” is compared to each possible expression from top to bottom. As soon as it finds an expression that is equal to the search-expression, its corresponding when-statements are executed and no further comparison to other expressions is performed. 

If no expression matching the search-expression is found, then the optional else-statements are executed. If no ELSE section has been added to the query and no match to the search-expression is found, a CASE-NOT-FOUND exception is raised.

Example of Simple Case Statement

Suppose the database administrator of a Movie Rental Store wishes to classify movies on the basis of their rental rate. The rental rate is an existing Attribute in the table and a new Attribute has to be created which shows the classification of that movie. The Postgres Case statement for it will be as follows:

CASE rate
        WHEN 0.99 THEN
     		price_segment =  'Mass';
        WHEN 2.99 THEN
          	price_segment = 'Mainstream';
        WHEN 4.99 THEN
                price_segment = 'High End';
        ELSE
	    	price_segment = 'Unspecified';
END CASE;

2) Searched Case Statement

The syntax for a Searched Case statement is as follows:

CASE
    WHEN boolean-expression-1 THEN
      statements
  [ WHEN boolean-expression-2 THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

In this type of Case statement, each boolean expression is evaluated from top to bottom. As soon as a boolean-expression evaluates to TRUE, the corresponding statements are executed and no further evaluation of boolean-expressions is performed.

The user can also choose to write an optional ELSE statement that will be executed if no boolean-expression evaluates to TRUE. In the absence of an ELSE statement and no boolean-expression evaluating to TRUE, a CASE-NOT-FOUND expression will be raised.

Example of Searched Case Statement

Suppose the database administrator of a Movie Rental Store wishes to classify movies as Short, Medium, or Long based on their length. The Postgres Case statement for it will be as follows:

CASE
     WHEN length> 0 AND length <= 50 
           THEN 'Short'
     WHEN length > 50 AND length <= 120  
           THEN 'Medium'
     WHEN length> 120 
           THEN 'Long'
END duration;

Limitations of Postgres Case Statement

Even though the Postgres Case statement has a lot of advantages, there are some limitations to using the statement which are as follows:

• A Postgres Case statement can have multiple boolean expressions that are evaluated from top to bottom but its execution for a particular record stops as soon as it gets the first TRUE result. This might lead to incorrect or partial results since it is possible that multiple expressions are TRUE for a particular record but never got evaluated.
• A Postgres Case statement cannot be used to evaluate NULL values in a table.

Conclusion

This article provided you with an in-depth understanding of what Relational Database Management Systems (RDBMS) are, why PostgreSQL is becoming the preferred choice for a large number of organizations, and what Postgres Case when multiple conditions statements are along with a simplified guide on how you can use them.

Evaluating large volumes of data spread across multiple databases to perform even a simple Postgres Case statement can be extremely difficult. Data from all your sources can be integrated using platforms like Hevo Data.

For any information on the comparative study of PostgreSQL vs MySQL, you can visit the former link.