PostgreSQL CASE with Examples

In this tutorial, we will learn about the PostgreSQL CASE conditional expressions. It is similar to IF\ELSE conditional statements used in any other programming language. It is used to form conditional queries in PostgreSQL. By the end of this tutorial, you will have complete knowledge of CASE conditional expressions in PostgreSQL.

• PostgreSQL CASE when
• CASE when multiple conditions in PostgreSQL
• PostgreSQL CASE statement in where clause
• PostgreSQL CASE insensitive
• PostgreSQL CASE sensitive
• PostgreSQL CASE when NULL or Empty
• PostgreSQL CASE insensitive like
• PostgreSQL CASE when multiple columns

PostgreSQL CASE When

In this section, we will learn about the PostgreSQL CASE conditional expressions. It is similar to IF\ELSE conditional statements used in any other programming language. These conditional expressions are one of the most fundamental parts of any programming language. It can be used with SELECT, WHERE, GROUP BY, and HAVING clauses.

The syntax for PostgreSQL CASE conditional expression is:

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

First, the CASE keyword is written which specifies the beginning of the CASE conditional expressions. Then, we specify the condition in the WHEN part and the result in the THEN part.

Since the condition in the WHEN part returns a boolean result, so if it evaluates to TRUE then it is followed by the result in the THEN part. If it returns FALSE then subsequent WHEN conditions are checked otherwise the result stored in the ELSE clause is used. Finally, we end the CASE expression by using the END keyword.

For the practical implementation of the CASE expression in PostgreSQL, First we will create a table:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

Now we will insert some data in this table using the INSERT statement in PostgreSQL:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

Now we will use the CASE expression like this in our example:

SELECT Emp_ID, Name,
CASE
WHEN Salary >= '3000' THEN 500
WHEN Salary >= '2500' THEN 750
WHEN Salary >= '2000' THEN 1000
END as Increment
From Employee;

In this example, we have used the CASE conditional expressions to evaluate the increment for every employee according to their salary. The employee with a salary ‘ 3000 or above ‘ will receive an increment of ‘ $500 ‘ and similarly employees with a salary ‘ 2500 or above ‘ and ‘ 2000 and above ‘ will get an increment of ‘ $750 ‘ and ‘ $1000 ‘ respectively.

In this section, we learned about the PostgreSQL CASE conditional expressions.

PostgreSQL CASE when multiple conditions

In this section, we will learn about PostgreSQL CASE when multiple conditions. We can apply multiple conditions in our CASE expression by using multiple WHEN and THEN statements. We can also use these CASE expressions within aggregate functions such as SUM, MIN, MAX, etc.

We have seen an example of the multiple conditions in the previous section itself, so here we will look at another example of multiple conditions in PostgreSQL CASE conditional expressions using aggregate functions.

The CASE Expression in PostgreSQL with multiple conditions inside aggregate functions is:

SELECT 
SUM(CASE
WHEN Dept='IT' THEN 1
ELSE 0
END)
AS IT_Dept,
SUM(CASE
WHEN Dept='Electrical' THEN 1
ELSE 0
END)
AS Electrical_Dept,
SUM(CASE
WHEN Dept='Mechanical' THEN 1
ELSE 0
END)
AS Mechanical_Dept,
SUM(CASE
WHEN Dept='HR' THEN 1
ELSE 0
END)
AS HR_Dept
FROM Employee;

This is the data inside the table, Now we will look at the result using the SELECT statement:

In this example, we have used the CASE expression to evaluate the sum of all the employees in a particular department. We have used the CASE expression inside the SUM function.

In this section, we learned about the PostgreSQL CASE when multiple conditions.

PostgreSQL CASE statement in where clause

In this section, we will learn about the PostgreSQL CASE statement in the WHERE clause. CASE expression can be used anywhere, such as SELECT, WHERE, GROUP BY, and HAVING clauses, where we compare two values. So here we will look at an example of CASE expression in WHERE clause.

PostgreSQL CASE expression in WHERE clause:

SELECT * 
FROM Employee
WHERE Emp_ID = CASE WHEN Dept='IT' THEN 1
ELSE 0
END;

In this example, we have compared the Emp_ID with the value generated by the CASE expression to find the employee whose department is ‘ IT ‘ and its Emp_ID is ‘ 1 ‘.

Though we can use a CASE expression to evaluate such a query but the best practice to find such a result is in such a way:

SELECT *
FROM Employee
WHERE Emp_ID = 1;

In this example, we get the same result as we got in our previous example using the CASE statement, but here we just had to compare a single value rather than processing the whole case expression.

In this section, we learned about PostgreSQL CASE statement in WHERE clause.

PostgreSQL CASE insensitive

In this section, we will learn about PostgreSQL CASE insensitive. PostgreSQL is a case-sensitive database but the unquoted text in PostgreSQL are insensitive.

Let us look at an example for PostgreSQL CASE insensitive :

SELECT Emp_ID,Name,
CASE Salary
WHEN '3000' THEN 1000
WHEN '2700' THEN 750
WHEN '2500' THEN 500
ELSE 0
END AS Increment
From Employee;

In this example we have written a CASE expression comparing the Salary column of the employee table, where we have specified the column as ‘ Salary ‘ in the CASE expression.

Now we will look at a similar expression with a slight change:

SELECT Emp_ID,Name,
CASE SALARY
WHEN '3000' THEN 1000
WHEN '2700' THEN 750
WHEN '2500' THEN 500
ELSE 0
END AS Increment
From Employee;

Now we have specified the column name as ‘ SALARY ‘ and we get the same result without any error because PostgreSQL is a case-insensitive language for unquoted text.

In this section, we learned about the PostgreSQL case-insensitive using a CASE expression.

PostgreSQL CASE sensitive

In this section, we will learn about the PostgreSQL CASE sensitive. We know that PostgreSQL is a case-sensitive database but it is insensitive for unquoted text. In this section, we will look at the same example as in the previous section but this time will specify the table in quoted text.

We will create a table with one of the columns using quotes so that we can create that particular column as case-sensitive, let us look at this example:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
"Salary" money,
Date_Of_Joining DATE);

We will describe the table using \d command along with the name of table:

\d Employee

When we specify the column name within quotation marks then that particular column will become case sensitive. Now we will add some data:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

Now we will have a look at the case-sensitive column using the PostgreSQL CASE expression:

SELECT Emp_ID,Name,
CASE "SALARY"
WHEN '3000' THEN 1000
WHEN '2700' THEN 750
WHEN '2500' THEN 500
ELSE 0
END AS Increment
From Employee;

In this CASE expression, we specified the column name in Upper Case and we got an error message, column ” SALARY ” does not exist. So now we know that this is a case-sensitive column name so we have to provide the exact column name in the CASE expression. This means:

SELECT Emp_ID,Name,
CASE "Salary"
WHEN '3000' THEN 1000
WHEN '2700' THEN 750
WHEN '2500' THEN 500
ELSE 0
END AS Increment
From Employee;

Now when we type the column name with the exact case then the CASE expression processes the data accordingly.

In this section, we learned about PostgreSQL case-sensitive using a CASE expression.

PostgreSQL CASE when NULL or Empty

In this section, we will learn about PostgreSQL CASE when NULL or Empty. We can use the CASE conditional expression to compare different values even if they are null. We will be looking at an example with a column having some null values.

Create a table in PostgreSQL using these lines of code:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE,
Dependents integer);

Now we will insert some data into this table using the INSERT statement having some NULL values as well:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25',3);
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12',1);
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21',NULL);
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10',2);
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16',NULL);
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07',1);
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13',NULL);
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05',2);
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24',NULL);
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23',3);

Use the SELECT statement to look at the successful insertion of data:

SELECT *
FROM Employee;

Now we will use the CASE expression in SELECT statement to look at the results in PostgreSQL:

SELECT Emp_ID, Name,
CASE 
WHEN Dependents IS NULL THEN 500
ELSE 1000
END as Increment
From Employee;

Here we want to increment the salary of employees based on the number of Dependents in their family, so if there are no dependents, i.e., NULL then there is a hike of ‘$500’ and others with dependents receive a hike of ‘$1000’.

In this section, we learned about PostgreSQL CASE when NULL or Empty.

Read: PostgreSQL WHERE with examples

PostgreSQL Case – Insensitive LIKE

In this section, we will learn about the PostgreSQL case-insensitive LIKE i.e., ILIKE in a CASE expression. ILIKE is used to compare a string value with a pattern with case-insensitive properties that is it does not compare string values according to their case, it only compares according to the pattern provided. We will look at an example of case-insensitive LIKE, i.e., ILIKE in a CASE Expression.

Create a table in PostgreSQL using these lines of code:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);

Now add some data into the table using INSERT statement in PostgreSQL using these lines of code:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','ELECtrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','ELECTRICAL',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');

We can look at the data in the table using SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;

Now we will use the ILIKE in CASE expression in PostgreSQL to compare a pattern without any case:

SELECT Emp_ID, Dept, Name,
CASE 
WHEN Dept ILIKE 'ELECTRICAL'THEN 700
ELSE 1000
END as Increment
From Employee;

Here we can see that the ILIKE clause ignores the case and looks for every possible match for the word ‘ELECTRICAL’ and the CASE expression processes the data accordingly.

In this section, we learned about PostgreSQL Case-Insensitive LIKE.

Read PostgreSQL WHERE IN

PostgreSQL CASE when multiple columns

In this section, we will learn about PostgreSQL CASE WHEN Multiple Columns. PostgreSQL CASE expression can be used to compare values according to many columns using AND or OR operators. We will look at an example where we will process according to two columns using the CASE expression in PostgreSQL.

The CASE Expression in PostgreSQL WHEN multiple columns is:

SELECT Emp_ID, Dept, Name,
CASE 
WHEN SALARY >= '2500' AND Date_Of_Joining > '2018-01-01' THEN 700
ELSE 1000
END as Increment
From Employee;

This is the data inside the table, Now we will look at the result using the CASE Expression in SELECT statement in PostgreSQL:

Here we have used the CASE expression to process data according to two columns and similarly, we can use as many columns in a CASE expression to get the results.

Watch my YouTube video for a better understanding of the topic:

You may like the following PostgreSQL tutorials:

• PostgreSQL DATE Format
• PostgreSQL INSERT INTO table

In this tutorial we learned, about PostgreSQL CASE . Also we have covered these topics:

• PostgreSQL CASE when
• PostgreSQL CASE when multiple conditions
• PostgreSQL CASE statement in where clause
• PostgreSQL CASE insensitive
• PostgreSQL CASE sensitive
• PostgreSQL CASE when NULL or Empty
• PostgreSQL CASE insensitive like
• PostgreSQL CASE when multiple columns