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);
PostgreSQL CASE When
PostgreSQL- Create Table

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');
PostgreSQL CASE When
PostgreSQL- Insert Data

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

SELECT * 
FROM Employee;
CASE When example
PostgreSQL- Data inside Table

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;
PostgreSQL CASE Expression
PostgreSQL- CASE Expression

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.

Read: PostgreSQL installation on Linux step by step

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;
CASE when multiple conditions
PostgreSQL- Data inside Table

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

PostgreSQL CASE Aggregate Function
PostgreSQL- CASE Expression inside Aggregate Function

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.

Read: How to create a table in PostgreSQL

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;
PostgreSQL CASE in WHERE clause
PostgreSQL- CASE in WHERE clause

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;
PostgreSQL WHERE clause without CASE
PostgreSQL- WHERE clause without CASE

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.

Read: How to connect to PostgreSQL database

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;
PostgreSQL CASE lower case
PostgreSQL CASE- Lower Case

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;
PostgreSQL CASE upper case
PostgreSQL CASE- Upper Case

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 .

Read: How to Uninstall PostgreSQL (Linux, Mac, and Windows)

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 column 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
PostgreSQL CASE sensitive
PostgreSQL CASE- Create Table

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');
PostgreSQL CASE sensitive example
PostgreSQL- Insert Data

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;
PostgreSQL CASE Error Message
PostgreSQL CASE- Error Message

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;
PostgreSQL CASE case sensitive
PostgreSQL CASE- Case Sensitive

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.

Read: How to Restart PostgreSQL (Linux, Windows, Mac)

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);
PostgreSQL CASE when NULL
PostgreSQL CASE- Create Table for NULL

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);
PostgreSQL CASE when Empty
PostgreSQL CASE- Insert Data with NULL values

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

SELECT *
FROM Employee;
CASE when NULL or Empty PostgreSQL
PostgreSQL CASE- Data in Table

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;
PostgreSQL CASE when NULL
PostgreSQL- CASE when NULL

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);
PostgreSQL Case example
Case Insensitive LIKE

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');
PostgreSQL Insert Data Case Insensitive
PostgreSQL- Insert Data Case Insensitive

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

SELECT * 
FROM Employee;
PostgreSQL CASE Data in Table case insensitive
PostgreSQL- Data in Table Case Insensitive

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;
PostgreSQL CASE case insensitive
PostgreSQL CASE- Case Insensitive

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;
PostgreSQL CASE when multiple columns
PostgreSQL- Data inside Table

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

PostgreSQL CASE When Multiple Columns
PostgreSQL CASE- When Multiple Columns

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.

You may like the following PostgreSQL tutorials:

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

Leave a Comment