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:
1 2 3 4 5 6 7 8 9 | DO $$ DECLARE x INTEGER := 1; y INTEGER := 2; BEGIN IF x < y THEN RAISE NOTICE 'x is lower than y'; END IF; END $$; |
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:
1 | NOTICE: x is lower than y |
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:
1 2 3 4 5 6 7 8 9 10 11 | DO $$ DECLARE x INTEGER := 1; y INTEGER := 2; BEGIN IF x > y THEN RAISE NOTICE 'x is larger than y'; ELSE RAISE NOTICE 'x is lower than y'; END IF; END $$; |
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:
1 | NOTICE: x IS LOWER than y |
IF-ELSE IF-ELSE Statement
Here’s an example of IF
, ELSE IF
, or ELSE
statements in psql:
1 2 3 4 5 6 7 8 9 10 11 12 13 | DO $$ DECLARE x INTEGER := 1; y INTEGER := 1; BEGIN IF x > y THEN RAISE NOTICE 'x is larger than y'; ELSE IF x < y THEN RAISE NOTICE 'x is lower than y'; ELSE RAISE NOTICE 'x is equal to y'; END IF; END $$; |
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:
1 | NOTICE: x IS equal TO y |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started