In many programming languages, the if-else statement is a common way of implementing conditions and making decisions given the premise. Based on a condition, the if-else statement decides what code to execute.
PostgreSQL also comes with its version of the if-else statement. In this guide, we will have a look at the IF ELSE statement in PostgreSQL.
IF ELSE in PostgreSQL
There are three forms of IF statements in PostgreSQL:
- IF
- IF THEN ELSE
- IF THEN ELSIF
The IF statement determines which statements to execute based on the result of a Boolean expression.
The basic structure of the IF statement is as follows:
<statements>;
END IF;
In the case of IF THEN ELSE, the structure is as follows:
<statements>;
ELSE
<alternate_statements>;
END IF;
For IF THEN ELSIF, the structure is as follows:
<statement_1>;
...
IF <condition_n> THEN
<statement_n>;
ELSE
<statement_else>;
END IF;
Prerequisites
To perform the steps demonstrated in this guide, you will need the following components:
- A properly-configured Linux system. Learn more about installing Ubuntu on VirtualBox.
- PostgreSQL installed and configured. Check out installing PostgreSQL on Ubuntu.
Implementing IF ELSE Statements
In this section, we will showcase a few examples of implementing the IF ELSE statements we went over so far. Most of the examples do not require any prior dataset.
Example 1 – Using IF
The example will feature a simple program: finding the largest of two numbers. We will declare two variables, assign values to them, compare their values, and output based on the comparison result.
DECLARE
var_a SMALLINT:= 65;
var_b SMALLINT:= 99;
BEGIN
IF var_a > var_b THEN RAISE NOTICE 'var_a > var_b';
END IF;
IF var_a < var_b THEN RAISE NOTICE 'var_a < var_b';
END IF;
IF var_a = var_b THEN RAISE NOTICE 'var_a = var_b';
END IF;
END $$;
Here,
- We’re comparing the value of two SMALLINT variables var_a and var_b.
- Upon comparison, we’re executing a RAISE NOTICE statement to declare the result of the comparison.
Example 2 – Using IF ELSE
While the previous example works just fine, it comes with a slight performance penalty. It tests the conditions for every single IF statement, consuming additional processing power.
For example, if var_a > var_b is TRUE, then there’s no need to process further IF statements, right? We only have to test further if var_a > var_b returns FALSE. The same goes for the other tiers of IF statements.
Let us upgrade the code using IF ELSE statement:
DECLARE
var_a SMALLINT:= 65;
var_b SMALLINT:= 99;
BEGIN
IF var_a > var_b THEN RAISE NOTICE 'var_a > var_b';
ELSE IF var_a < var_b THEN RAISE NOTICE 'var_a < var_b';
ELSE IF var_a = var_b THEN RAISE NOTICE 'var_a = var_b';
END IF;
END IF;
END IF;
END $$;
Here,
- The program will always enter the first IF statement.
- The second and third IF statements will be executed only if the previous IF statements are FALSE.
Example 3 – Using IF ELSIF
We can further optimize the program by using IF ELSIF statement. Check out the code:
DECLARE
var_a SMALLINT:= 65;
var_b SMALLINT:= 99;
BEGIN
IF var_a > var_b THEN RAISE NOTICE 'var_a > var_b';
ELSIF var_a < var_b THEN RAISE NOTICE 'var_a < var_b';
ELSIF var_a = var_b THEN RAISE NOTICE 'var_a = var_b';
END IF;
END $$;
Here, we only wrote a single IF statement, thus reducing the need for additional END IF statements.
Conclusion
In this guide, we demonstrated various forms of IF ELSE statements found in PostgreSQL. We also showcased their usage using a simple example.
PostgreSQL also comes with a ton of pre-defined functions for more efficient queries. In fact, we can also construct more complex queries using sub-queries.
Interested in learning more about PostgreSQL? Check out the PostgreSQL sub-category, abundant with guides on various aspects of PostgreSQL.
