Postgresql If Else Statement

In this PostgreSQL tutorial, I will show how to use Postgresql If Else Statement to control the flow of the program.

Also, I will introduce to you three variants of Postgresql If Else and these variants are IF only and IF ELSE, and IF ELSE with ELSEIF to handle one or multiple conditions.

Introduction to Postgresql If Else Statement

PostgreSQL If statement gives you control to execute the command or query based on the given condition.

The If controls the flow of the program in PostgreSQL like the If Else statement in other programming languages. It allows you to execute a specific query for a specific condition.

Here the condition is a boolean expression that is a true or false value, if the condition is true then the query for this condition is executed otherwise query within ELSE gets executed. There are three variants of the If statement:

First variant:

IF contion_or_expression THEN
--- Write here the query or command that you want to execute
END IF;

This variant of IF is used where you need to execute the command and query for the specific condition only, if the condition is not met then the execution of the query should not have any effect.

Second variant:

IF contion_or_expression THEN
--- Write here the query or command that you want to execute
ELSE
--- Write here the query or command that you want to execute
END IF;

This variant of IF ELSE provides you more control over the flow of the program compared to only the first variant of IF. If the query or command doesn’t execute due to a false condition of the IF statement, then the query within ELSE gets executed.

Third variant:

IF contion_or_expression THEN
--- Write here the query or command that you want to execute
ELSE IF THEN
--- Write here the query or command that you want to execute
ELSE
--- Write here the query or command that you want to execute
END IF;

This is the nested variant of the IF ELSE statement, it gives you more control over the flow of the program compared to the second variant of IF. This is used where you know there can be multiple situations for the execution of different queries or commands.

Let’s take some examples and for the example, you will use the table ’employees’ which is shown below.

Postgresql If Else Statement

I told you already in the intro section that PostgreSQL if else gives you more control over the flow of the program. Moreover, if certain conditions are met then execute this query, if not met then execute that query.

Suppose you have a table ’employee’ with columns ‘name’, ‘department’, and ‘salary’ and you want to increase the salary to $500 for the employee who belongs to the sales department and whose salary is less than $60000. So you can use the below command.

do $$
begin  
 
  If exists (SELECT FROM employees WHERE department='Sales') THEN
	UPDATE employees SET salary=salary + 500 
	WHERE department='Sales' and salary <= 60000;
  ELSE
   raise notice'No employee is found with salary less than equal to 60000';
   
  end if;
end $$

If you look at the above output, the salary increment for employees Tims and Tonny of the sales department, and the code within the IF statement executed to update the salary.

But if it fails to find the employee whose salary is less than 60000 then the code within ELSE gets executed. Let’s take one more example and see how the ELSE block gets executed.

Now suppose you want to increase the salary to $500 for an employee who belongs to department sales and a salary is greater than $70000, otherwise, if there is no employee with a salary greater than $70000, then increase the salary to $5000 for each employee of sales department.

do $$
begin  
 
  If exists (SELECT FROM employees WHERE department='Sales' and salary > 70000) THEN
		UPDATE employees SET salary=salary + 500 
		WHERE department='Sales';
  ELSE
	    UPDATE employees SET salary=salary + 5000 
		WHERE department='Sales';
   
  end if;
end $$

SELECT * FROM employees;

Again look at the above code, the salary of employees Tims and Tonny incremented by $5000 because this time the code within the ELSE block gets executed. From the first example, you know that there wasn’t any employee with a salary greater than or equal to $70000 in the sales department.

So the condition within IF (If exists (SELECT FROM employees WHERE department=’Sales’ and salary > 70000)) is evaluated as false and the code within ELSE (UPDATE employees SET salary=salary + 5000 WHERE department=’Sales’) gets executed.

Postgresql If Else Statement using only IF Statement

In this section, I will show you the PostgreSQL if else example using only the IF statement. Consider you have two variables containing an integer value and you want to find which value is smaller, for that, you can use the IF statement to compare the value and get the output to know which one is smaller.

Use the below command to find the smaller value between two values.

do $$
declare
    value_1 integer := 20;
    value_2 integer := 50;
begin  
  
  if value_1 < value_2 then
     raise notice'The value_1 is smaller than value_2';
  end if;
end $$

The two variables value_1 and value_2 are initialized with values 20 and 50 respectively in the declare section of the above code. Then uses the IF statement to compare the values ( if value_1 < value_2 then) within begin section of the code.

Here the conditional expression is value_1 < value_2 and this condition evaluates to True, so the line or code (raise notice’The value_1 is smaller than value_2′) gets executed and outputs the result as (NOTICE: The value_1 is smaller than value_2) as you can see in the above output.

I have shown you the simple PostgreSQL If else examples with variables only, you can use them with tables or columns according to your need.

Nested Postgresql If Else Statement

You can check multiple conditions using Postgresql If Else with ELSEIF THEN. Basically, the multiple conditions are specified with their code using ELSEIF THEN, and the code gets executed whichever condition is met accordingly.

If none of the conditions are met, then only the query or code within the ELSE block is executed. Here you will use the same examples as you have used in the PostgreSQL If else statement section.

Suppose you want to increase the salary of $1000 and $500 of the employee with a salary of less than $50000 and greater than $50000 respectively. So use the below code.

do $$
begin  
 
  If exists (SELECT FROM employees WHERE department='Sales' and salary < 50000) THEN
		UPDATE employees SET salary=salary + 1000 
		WHERE department='Sales';
  ELSEIF exists(SELECT FROM employees WHERE department='Sales' and salary > 50000) THEN 
	    UPDATE employees SET salary=salary + 500 
		WHERE department='Sales';
  ELSE
    RAISE NOTICE 'No employee in sales and marketing with salry less than 50000 and 70000 respectively';
  end if;
end $$

SELECT * FROM employees;

In the above code, first the condition (If exists (SELECT FROM employees WHERE department=’Sales’ and salary < 50000)) within If is checked and it evaluates to false because there isn’t any employee with a salary less than $50000 in the sales department.

Then next condition (ELSEIF exists(SELECT FROM employees WHERE department=’Sales’ and salary > 50000)) within ELSEIF is checked and it evaluates to true. So the employee with a salary greater than $50000 in the sales department gets an increment of $500 by executing the code within the ELSEIF block.

So using PostgreSQL If else with ELSIF, you can use multiple conditions by specifying ELSIF to perform the different operations on the database accordingly or execute the query according to the condition to get the desired result.

Conclusion

In this PostgreSQL tutorial, you have covered how to implement Postgresql If Else Statement to handle multiple conditions and execute the query or command accordingly. Additionally, you learned about the three forms of Postgresql If Else.

You may also read:

• How to Find PostgreSQL DateDiff
• How to Define Auto Increment in PostgreSQL
• How to use PostgreSQL current_timestamp