EDUCBA

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL CASE Statement

PostgreSQL CASE Statement

Introduction to PostgreSQL CASE Statement

PostgreSQL case statement is the same as the if-else statement defined in other languages like C and C++. PostgreSQL provides two forms or types of a case statement first is a general form case statement, and the second is a simple form of the case statement. We can use the case statement in PostgreSQL using a when and then keyword like if and else in other programming languages. The case statement is significant in PostgreSQL to formulate the conditional expression; we formulate the conditional by using when and then keyword in PostgreSQL.

Syntax

Below is the syntax of the case statement.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

1. General Case Expression

Below syntax shows a general case expression.

  • CASE(starting Case statement)
  • WHEN (When keyword used to formulate the condition) condition_1 THEN(Then keyword used to formulate the condition)result_1 (Result of first case statement)
  • WHEN (When keyword used to formulate the condition)condition_2 THEN(Then keyword used to formulate the condition)result_2 (Result of second case statement)
  • [WHEN …] (We can use multiple conditions in one case statement)
  • [ELSE result_n] (If the case statement result fails, then execute this statement)
  • END (End of case statement)

2. Simple Case Expression

The below syntax shows simple case expression.

  • CASE expression
  • WHEN (When keyword used to formulate the condition)value_1 THEN(Then keyword used to formulate the condition)
  • result_1(Result of first case statement)
  • WHEN(When keyword used to formulate the condition) value_2 THEN(Then keyword used to formulate the condition)
  • result_2 (Result of second case statement)
  • [WHEN …](We can use multiple conditions in one case statement)
  • ELSE
  • result_n
  • END;(End of case statement)
Parameter

Below is the parameter description of the above syntax.

  • Case: We can start the case statement in PostgreSQL by using a case keyword. The body of the case statement will start with the case and end with the END keyword.
  • When: When the keyword is used to formulate the condition of the case statement in PostgreSQL.
  • Then: Then, a keyword is used to formulate the condition of the case statement in PostgreSQL.
  • Condition 1 and condition 2: We can use a condition statement to retrieve the data’s result. If one condition fails, the trigger goes to second; if it is true, it will display the result of all conditions.
  • Result 1 to Result N: This is the actual result of the case statement in PostgreSQL.
  • Else: Else keyword is used to define the true or false condition in the case statement. If the case statement condition is false, then else part will execute; otherwise, it is not executing.
  • End: We can end the case statement in PostgreSQL by using the end keyword. The body of the case statement will start with the case and end with the END keyword.
  • Value 1 and value 2: Value is nothing but a condition in the case statement.

Flowchart

The below diagram shows the flowchart of the case statement.

Flowchart

  • The above flowchart states that the case statement starts with the case or start a keyword. Normally we have used a case keyword to start the case statement.
  • After that cursor goes to a condition that we have used in the statement. If the given condition is true, then it will execute a statement that we have written in the case statement.
  • If the condition is false, then the cursor directly goes to the end statement.
  • Before executing any case statement, we need to define the flowchart of the case statement.
  • The flowchart is nothing but the pictorial representation of a case statement that we have used in our query.
  • The flowchart is most important and useful while creating a case statement in PostgreSQL.

How does the CASE statement work in PostgreSQL?

Below is the working:

  • When and then, the keyword is used to formulate the condition of the case statement.
  • We can use a condition statement to retrieve the result of the data. If one condition fails, the trigger goes to second; if it is true, it will display the result of the first condition. The conditional expression is most important in the case statement to display the result.
  • Else keyword is used to define the true or false condition in the case statement. If the case statement condition is false, then else part will execute; otherwise, it is not executing in the PostgreSQL case statement.
  • We can start the case statement in PostgreSQL by using a case keyword and end with the end keyword. The body of the case statement will start with the case and end with the END keyword.
  • PostgreSQL case statement is the same as the if-else statement defined in another language like C and C++.
  • The case statement was significant in PostgreSQL to formulate the conditional expression; we formulate the condition by using when and then keyword.
  • PostgreSQL provides two forms or types of a case statement first is a general form case statement, and the second is a simple form case statement.
  • We can use the case statement in PostgreSQL using a when and then keyword like if and else in other programming languages.

Examples to Implement PostgreSQL CASE Statement

Below are the examples as follows. We have using the employee table to describe the example of the case statement.

Code:

select * from employee;

Output:

PostgreSQL CASE Statement - 2

1. General PostgreSQL case expression

The below example shows general PostgreSQL case expression. In this example, we find the employee’s good, better and best salary.

Code:

SELECT
SUM (CASE WHEN emp_salary = 20000 THEN 1
ELSE 0
END) AS "EMP salary is good",
SUM (CASE WHEN emp_salary = 35000 THEN 1
ELSE 0
END) AS "EMP salary is better",
SUM (CASE WHEN emp_salary = 55000 THEN 1
ELSE 0
END) AS "EMP salary is best"
FROM Employee;

Output:

General

2. Simple PostgreSQL case expression

The below example shows a simple PostgreSQL case expression. In this example, we find the employee’s good, better and best salary.

Code:

SELECT
SUM (CASE emp_salary WHEN 20000 THEN 1
ELSE 0
END) AS "EMP salary is good",
SUM (CASE emp_salary WHEN 35000 THEN 1
ELSE 0
END) AS "EMP salary is better",
SUM (CASE emp_salary WHEN 55000 THEN 1
ELSE 0
END) AS "EMP salary is best"
FROM Employee;

Output:

Simple

Conclusion

When and then, the keyword is used to formulate the condition of the case statement. The case statement is the same as the if-else statement defined in another language like C and C++. We can start the case statement in PostgreSQL by using the case keyword and end with the end keyword.

Recommended Articles

This is a guide to PostgreSQL CASE Statement. Here we discuss syntax PostgreSQL CASE Statement, along with proper flow chart and respective examples. You can also go through our other related articles to learn more –

  1. PostgreSQL OFFSET
  2. PostgreSQL NATURAL JOIN
  3. PostgreSQL Subquery
  4. PostgreSQL COMMIT
  5. Examples of PostgreSQL Queries
0 Shares
Share
Tweet
Share