Postgres Conditional Statements

Introduction

When you’re using PostgreSQL to manage your data, there may be situations where you want to maintain some control over the execution of certain commands or functions. You can exercise the control you need with the help of Postgres conditional statements. These statements help ensure that actions are executed only if certain conditions are met. In this article, we will discuss Postgres conditional statements and provide some examples to help you understand how they work and when to use them.

Prerequisite

Before attempting to follow along with the examples shown in this tutorial, make sure that PostgreSQL is installed and configured on your machine.

The Postgres Conditional Statement

You can think of conditional statements as roadmaps or flowcharts– they help determine which actions should be executed instead of just proceeding with the execution of a certain function. Creating a conditional statement is fairly simple; you just need to define any conditions that must be met and what actions should be taken.

Creating Sample Dataset

In this section, we’ll create a sample dataset that we can use throughout this tutorial. Here’s the SQL statement we’ll use to create a table:

1
2
3
4
5
CREATE TABLE tblsample (
   name varchar (50),
   age int,
   gender varchar (8)
) ;

Now, let’s insert records:

1
2
3
4
5
6
7
INSERT INTO tblsample (name,age,gender)
VALUES  ('john', 4, 'male'),
        ('james', 6, 'male'),
        ('greg', 5, 'male'),
        ('jane', 4, 'female'),
        ('denise', 6, 'female'),
        ('jessy', 5, 'female');

The output should look like something like this:

1
2
3
4
5
6
7
8
9
10
dbsample=# select * from tblsample;
  name  | age | gender
--------+-----+--------
 john   |   4 | male
 james  |   6 | male
 greg   |   5 | male
 jane   |   4 | female
 denise |   6 | female
 jessy  |   5 | female
(6 rows)

IF/THEN Statement

The IF/THEN statement allows us to define statements in a block format that will be executed only when a set of conditions turn out to be true. Shown below is the basic form of the IF/THEN statement:

1
2
3
IF <the_condition> THEN
   <the_statement>;
END IF;
  • The the_condition is a boolean expression that can be evaluated to produce a value of true or false.
  • The the_statement is the part of the code that gets executed when the condition turns out to be true. Be aware that the_statement can be another conditional statement or a nested IF statement.

IF/THEN Statement Example

In this section, we’ll look at the basic syntax of the IF/THEN statement and learn how to use this type of Postgres conditional statement.

Let’s look at the statement shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DO $$
# // here we declare the X and Y variable and assign corresponding value.
DECLARE
  X integer := 100;
  Y integer := 200;
BEGIN
  # // This first statement will check if the value of X is greater than the value of Y
  IF X > Y THEN
  # This will prompt us if the above condition evaluates to true
   RAISE NOTICE 'X is greater than Y';
  # This will end this statement and proceed to the next statement.
  END IF;

  IF X < Y THEN
  # This will prompt us if the above condition evaluates to true
   RAISE NOTICE 'X is less than Y';
  END IF;

  IF X = Y THEN
  # This will prompt us if the above condition evaluates to true
   RAISE NOTICE 'X is equal to Y';
  END IF;
END $$;

Now that we have a basic understanding of how the IF/THEN statement works, let’s try to use the following command on the table that we created in the previous section:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE FUNCTION cts(varchar) RETURNS TEXT AS'
 DECLARE
  b_gender ALIAS FOR $1;
  cnts integer;
 BEGIN
 SELECT INTO cnts count(*) FROM tblsample WHERE gender = b_gender;
 IF cnts > 0 then
   Return cnts;
 END IF;

 END;
 '
LANGUAGE 'plpgsql';
  • This code will create a function called ‘cts’ that accepts an argument of type varchar.
  • We start by using the DECLARE block to declare our variables.
  • We then BEGIN our query, which will be used to count the number of records based on the value of the gender field.
  • The IF/THEN statement will only be executed if the number of records are greater than zero.

PostgreSQL will let us know that the function was created after we execute the above query, with a response like this: CREATE FUNCTION.

To execute the function shown above, we use the following code: SELECT cts('female');

This command calls the cts function and specifies a value for the gender; in this case, we’re using the value female.

The output should look like this:

1
2
3
4
5
dbsample=# SELECT cts('female');
 cts
-----
 3
(1 row)

We can verify that this result is accurate by with a SELECT statement:

1
2
3
4
5
6
7
8
9
10
dbsample=# SELECT * FROM tblsample;
  name  | age | gender
--------+-----+--------
 john   |   4 | male
 james  |   6 | male
 greg   |   5 | male
 jane   |   4 | female
 denise |   6 | female
 jessy  |   5 | female
(6 ROWS)

We can see that we have three females in the table, so our result was indeed accurate.

Conclusion

Postgres conditional statements can help you control what actions are taken against your database. With a conditional statement in place, you can ensure that specified actions will be performed only when certain conditions prove to be true. In this article, we looked at some typical examples of IF/THEN statements in PostgreSQL. With these examples to serve as a guide, you’ll be able to implement conditional logic in your own PostgreSQL database tasks.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.