Postgres If Statement

Introduction

When you’re working with PostgreSQL, you may want to ensure that a command only executes if certain conditions are met. In these cases, the Postgres IF statement can provide the control you need. This statement uses boolean values to determine whether or not to execute a command. The term boolean refers to a data type that has a value of true or false. If the value of the boolean is true, the IF statement will execute an action based on the statement assigned.

To execute the statement, you’ll need to create an ad-hoc statement using the do command. You’ll also need to add the end keyword at the end of the statement. There are three basic forms of the IF statement: IF, IF-ELSE, and IF-ELSE IF-ELSE. In this article, we’ll discuss all three forms of the Postgres IF statement and look at some examples of how they’re used.

Prerequisites

Before you can test out any IF statements in PostgreSQL, make sure that you have PostgreSQL installed on your computer. You’ll need it to run the SQL shell where we’ll execute our queries. If you don’t have Postgres installed yet, you can download it from their website. You’ll also need to have some basic knowledge of PostgreSQL to follow along with the examples in this tutorial.

IF Statement

The flowchart shown below illustrates the logic behind an IF statement in Postgres:

The following command is an example of a simple IF statement that you’d execute in psql:

NOTE: We used the DECLARE command in the code above to declare the values of the two variables, x and y.

In the IF statement shown above, we compare the x and y variables using the < operator. If the value of x is lower (<) than the value of y the text supplied in the command will be the output. Because the value of the variable x is indeed less than y, our output will be:

Postgres IF ELSE Statement

Here’s an example of an IF statement used in conjunction with the SQL ELSE keyword in a Postgres SQL statement:

NOTE: Be sure to end the block statement of IF keywords using the END IF SQL keyword.

In this example, we changed the comparison operator < to >. This means that if the value of x is larger (>) than the value of y the output will be x is larger than y; otherwise, it will be x is lower than y. Since the value of x is less than the value of y, the output will be:

IF-ELSE IF-ELSE Statement

Here’s an example of IF, ELSE IF, or ELSE statements in psql:

In the example shown above, if the value of x is greater (>) than the value of y the output will be x is larger than y. If x is lower, the output will be x is lower than y. Otherwise, the output will be x is equal to y. Since the two variables have the same value in our example, the output will be:

Conclusion

When you need to exercise some control over the execution of commands, the Postgres IF statement is the right tool for the job. With an IF statement in place, a command will only execute if certain conditions are true. You can structure an SQL IF statement in a few different ways: IF, IF-ELSE and IF-ELSE IF-ELSE. You can also use a nested IF statement inside of another IF statement. With the examples shown in this tutorial, you’ll be able to implement the IF statement in your own PostgreSQL database work.