PostgreSQL offers some control statements such as “if”, “if then else”, and “if then elsif” that are used to control the flow of a program. These statements are also known as conditional statements or control statements. All these statements execute a command or set of commands based on a specific condition.

This write-up will teach us how to use the if then else statement in PostgreSQL with the help of practical examples.

How to Use If Else Statements in PostgreSQL?

In Postgres, the if statement checks a condition/criteria and returns true or false. In PostgreSQL, when a condition is false, the if statement does not handle it. Therefore, to handle the false conditions, the else statement is used in Postgres.

if condition then
statements/commands;
else
alternate-statements/commands;
END if;

The syntax illustrates that the statements/commands affiliated with the if statement will execute only if the given condition/expression is true. However, the statements specified within the else block will get executed when the given condition is false.

Let’s understand it practically.

Example #1: How Does the IF Else Statement Work in Postgres?

Let’s create a variable “std_age” and assign it some values. Next, the if statement will get executed to check if the given condition is true or not. The notice “student under 18” will appear if the specified condition is true, else you will see a notice “student over 18”:

DO $$
DECLARE std_age INT:= 20;
BEGIN
IF std_age <= 18 THEN
RAISE NOTICE 'student under 18';
ELSE
RAISE NOTICE 'student over 18';
END IF;
END $$;

The specified condition was false, so the else part gets executed.

Example #2: How Does the IF Else Statement Work on Table’s Data?

We have created a table named bike_details, whose details are shown in the following snippet:

SELECT * FROM bike_details;

If a bike with white color exists in the targeted table, then we will replace/update it with blue. Else if the white bike doesn’t exist in the bike_details table, then we will insert a new bike having a blue color:

DO $$
BEGIN
IF EXISTS (SELECT FROM bike_details WHERE bike_color='White') THEN
UPDATE bike_details SET bike_color='Blue' WHERE bike_color='White';
ELSE
insert into bike_details values (12,'Blue',2022,'ABC 111','2022-08-25','160000');
END IF;
END $$;

Let’s check the updated records using the below command:

SELECT * FROM bike_details;

The updated table proves that all the white bikes have been updated to blue color. Now in the bike_details table there is no bike that has a white color.

Let’s run the following statement one more time to see how the if-else statement deals with the false condition:

DO $$
BEGIN
IF EXISTS (SELECT FROM bike_details WHERE bike_color='White') THEN
UPDATE bike_details SET bike_color='Blue' WHERE bike_color='White';
ELSE
insert into bike_details values (12,'Blue',2022,'ABC 111','2022-08-25','160000');
END IF;
END $$;

In this example, there is not a single bike having white color, so this time else part will get executed, and hence a new record will be inserted into the bike_details table:

Let’s check the updated content of the bike_details table using the below command:

SELECT * FROM bike_details;

A new record has been inserted into the bike_details table. It proves that this time the else part gets executed.

Conclusion

In Postgres, the if statement checks a condition/criteria and returns true or false. In Postgres, the if statement doesn’t handle the false condition. To handle the false conditions, the else statement is used in PostgreSQL. Therefore, the statements/commands affiliated with the if statement will execute only if the given condition/expression is true. However, the statements specified within the else block will get executed when the given condition is false. This write-up considered various examples to explain the working of the if else statement.