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, IF ELSE, and IF ELSE with ELSEIF to handle one or multiple conditions.

PostgreSQL If Else Statement

Introduction to PostgreSQL If Else Statement

PostgreSQL’s IF-ELSE statements are primarily used within PL/pgSQL, the procedural language extension of PostgreSQL. These conditional statements allow you to execute different blocks of code based on specific conditions, much like in traditional programming languages.

Here, the condition is a Boolean expression that is a true or false value. If the condition is proper, then the query for this condition is executed; otherwise, the query within the ELSE gets executed.

Now, let’s explore the various forms of IF statements in PostgreSQL. There are three variants of the if statement:

First variant:

Syntax

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

This variant of IF is used when you need to execute a command and query for a specific condition only; if the condition is not met, the query’s execution should have no effect.

Second variant:

Syntax

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 the ELSE gets executed.

Third variant:

Syntax

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, which provides more control over the program’s flow compared to the second variant of the IF statement. This is used when you know there can be multiple situations for executing different queries or commands.

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

I mentioned in the introduction section that PostgreSQL’s if-else statements give you more control over the program’s flow. Moreover, if certain conditions are met, then execute this query; if not, 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. You can use the command below.

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 $$

After executing the above query, I got the expected output as shown in the screenshot below.

As shown in the output above, the salary increments for employees Tim and Tony of the sales department, along with the code executed within the IF statement, are used to update their salaries.

However, if the code fails to find the employee whose salary is less than $60,000, then the code within the ELSE block 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 in the sales department whose salary is greater than $70,000. Otherwise, if there is no employee with a salary greater than $70,000, then increase the wage to $5,000 for each employee in the 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;

After executing the above query, I got the expected output as shown in the screenshot below.

Again, refer to the code above. The salaries of employees Tim and Tony were increased by $5000 because this time the code within the ELSE block was executed. From the first example, you know that there was no employee in the sales department with a salary greater than or equal to $70,000.

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.

Example 1: Using only an IF Statement

In this section, I will show you a 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 values 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 $$

After executing the above query, I got the expected output as shown in the screenshot below.

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, it uses the IF statement to compare the values (if value_1 < value_2) within the 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 needs.

Example 2: Nested PostgreSQL If Else Statement

You can check multiple conditions using PostgreSQL IF-ELSE with ELSEIF THEN. Essentially, multiple conditions are specified with their corresponding code using ELSEIF THEN, and the code is executed according to whichever condition is met.

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 employees by $ 1,000 and $500, respectively, for those with a salary of less than $50,000 and greater than $50,000. 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;

After executing the above query, I got the expected output as shown in the screenshot below.

In the above code, the condition (if exists (SELECT FROM employees WHERE department=’Sales’ and salary < 50000)) within the if statement is checked, and it evaluates to false because there is no employee with a salary less than $ 50,000 in the Sales department.

The following condition (ELSEIF exists(SELECT FROM employees WHERE department=’Sales’ and salary > 50000)) within ELSEIF is checked, and it evaluates to true. Therefore, the employee with a salary greater than $50,000 in the sales department receives an increment of $500 upon executing the code within the ELSEIF block.

Using PostgreSQL, you can utilize multiple conditions with an if-else statement, including ELSIF, to perform different operations on the database accordingly. This allows you to execute the query based on the condition to obtain the desired result.

Best Practices for Using IF-ELSE in PostgreSQL

Here are some best practices to follow:

1. Keep conditions simple: Complex conditions can be hard to debug and maintain
2. Use indentation: Proper indentation makes nested IF statements more readable
3. Consider CASE when appropriate: For simple value-based conditions in queries, CASE is often cleaner
4. Handle NULL values explicitly: NULL can cause unexpected behavior in conditions
5. Include ELSE clauses: Always include ELSE clauses to handle all possible conditions
6. Validate inputs: Use IF statements to validate function inputs before processing
7. Add comments: Document complex conditional logic for future reference

Performance Considerations

When working with IF statements in PostgreSQL functions, keep these performance aspects in mind:

• Short-circuit evaluation: PostgreSQL evaluates boolean expressions from left to right and stops as soon as the result is determined. For example, in the IF condition1 AND condition2 THEN statement, if condition1 is false, condition2 won’t be evaluated.
• Complex conditions: Very complex conditions can impact performance. Consider breaking them down into multiple simpler IF statements.
• Excessive nesting: Deeply nested IF statements can make code difficult to maintain and might impact performance. Consider refactoring complex decision trees.
• Function calls in conditions: Be cautious with function calls in IF conditions, especially in loops, as they can significantly impact performance.

Conclusion

In this PostgreSQL tutorial, you have covered how to implement PostgreSQL’s 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