Postgres CASE Expression

You need to use an expression for your conditional queries in a statement, which can be done using If/ Else statements. But PostgreSQL doesn’t support If/ Else statements, the alternative for If/ Else statements in PostgreSQL is to use the “CASE” expressions. The “CASE” expression in PostgreSQL allows you to use If/ Else statements as a conditional expression in which you can execute queries using “WHEN” and “THEN” in each “CASE” statement. The “CASE” expression can be used with other expressions as well, such as: “SELECT”, “HAVING”, “GROUP BY”, and “WHERE” clauses. In this article, we will be gaining knowledge on different forms of “CASE” statements and demonstrating various examples of “CASE” statements using “WHEN” and “THEN” cases in examples which will make it much easier for understanding.

Forms of CASE Expression:

The “CASE” expression consists of two forms which are:

1. Simple “CASE” expression statements.
2. General/ Searched “CASE” expression statements.

Both forms define a conditional expression in a query which we will look into in further examples in this article.

1. Simple “CASE” Expression Statements in PostgreSQL:

The first form of the “CASE” statement basic syntax is mentioned below:

In this statement, the “CASE” expression will first execute and evaluate the ‘expression’ and differentiate the results with the “WHEN” clause (expression1, expression2, etc.). If it finds the match, then the “WHEN” clause will be executed.

If there are no matches found, then the “ELSE” clause will be executed with the ‘else-statements’ in it. It is unnecessary to include the “ELSE” clause in the query, as it is optional, but if there are no “ELSE” clauses and the match is still not found, PostgreSQL will give an exception error.

To run queries and to include conditional expressions in your statement we first have to create a sample table to put “CASE” conditions on it in PostgreSQL. I have generated a table as an example:

After inserting some values in the above table, the table looks like this:

Now, when the table is created, let’s dive into some examples for simple PostgreSQL “CASE” expressions below:

Simple “CASE” Expression Examples:

In the table created above, “passengers_info”, we will use the simple “CASE” expressions in the statements. In the below example, I have used the “CASE” expression to include in what age era the passengers are in; for example, a person whom age is ‘22’ is displayed as ‘in twenties’ age and so on. Below is the demonstration of this example:

In the above statement,

• The “SELECT” clause selects the columns that need to be displayed in the output.
• In the “CASE” expression, the condition in the “WHEN” clause is compared if there is any passenger with age less than 30, then put that in the results of that passenger ‘in twenties’ and so on.
• The “END” clause is to end the condition, and ‘Age_era’ gives the name to the column in the output. You can specify any other name accordingly.
• The “FROM” clause will extract the data from the table that is mentioned.
• Lastly, the “Order by” clause is optional; it is used to order the results in increasing order with “Id”.

The above statement gives the following output on the screen:

As we can see, the passengers in their twenties, thirties, or middle-aged era are displayed respectively with their Ids and name column, and the column name we included in the “END” clause is also visible.

Simple “CASE” Expression with SUM Function in PostgreSQL:

We can use aggregate functions like COUNT, SUM, AVG, MIN, MAX etc. functions in our statements too within “CASE” expressions. In the below example, we will be seeing how you can use “SUM” function in “CASE” condition statements:

The above syntax uses the “SUM” function to calculate how many passengers travel to a specific city. The output for the following syntax is attached below:

This output result shows the number of passengers traveling to which city.

2. General/ Searched “CASE” Expression Statements in PostgreSQL:

The following syntax shows the basic statement to write general or searched “CASE” expressions in PostgreSQL:

In searched or general “CASE” expressions, the conditions are Boolean expressions, and “CASE” expressions are dependent on these Boolean expressions. The “CASE” expression evaluates each condition in the statement until the ‘True’ condition is founded. If it finds the true condition at some point, then it doesn’t move on to the next expression and returns the true condition result. If no condition is found to be true, then it executes the “ELSE” clause statement or returns null if no “ELSE” condition is mentioned.

General “CASE” Expression with SUM Function in PostgreSQL Example:

In this example we will divide and calculate the passengers travelling into two classes i.e. economy and business class using “SUM” function:

In the above statement, we have used the condition that passengers with a payment of less than 1 lac travel to economy class and greater than 1 lac will travel to business class. The output is displayed as:

Conclusion:

In conclusion, we have learned in this article how to use “CASE” expressions in PostgreSQL. There are two forms of “CASE” expression, and both can be used to get the desired outcome. We also have executed some examples using “CASE” with “WHEN” and “THEN” clauses in statements to implement the syntax of the “CASE” expression in PostgreSQL.