Using Postgres While Loop in SQL

Introduction

In this tutorial, we learn to query Postgres with the While loop. We’ll include:

  • What? What does “WHILE” actually do and what is the syntax?
  • Why and how? Why and how do we use a “WHILE” loop and where/when can we use it in the SQL we write?

Prerequisites

  • Knowledge of how to execute SQL statements, either with PostgreSQL (or Oracle, MS SQL Server, MySQL, etc.) alone, using PG Admin or some other database administration tool, or even by using a coding language (like Python, Node, PHP, Java, etc.) that provides a connection to your database as well as a method for sending it SQL (or T-SQL) commands, to make changes to a database.
  • Beginner level knowledge of basic SQL commands, including SELECT, FROM, and INSERT.

What is the “WHILE” statement? How does it work?

In PostgreSQL, the WHILE structure defines a condition and loop. The syntax is as follows:

WHILE [condition]
    LOOP
        [statements]
    END LOOP

Info tidbit: In some other relational database forms of SQL, including Microsoft’s MS SQL Server, the structure of a WHILE BEGIN END loop is as follows:

WHILE [condition]
    BEGIN
        [statements]
    END

Info tidbit: In MySQL, the WHILE LOOP syntax is:

WHILE [condition]
    DO
        [statements]
    END WHILE

Here’s a flowchart to give you a visual representation of the program flow of a WHILE loop:

Image from Gyazo

Why and how do we use “WHILE” in our SQL?

It’s important to consider that When writing SQL, a HAVING or WHERE clause will usually suffice for limiting which rows are returned or acted upon. Keep this in mind! Often times, a WHILE LOOP will add unnecessary complexity to your query or code. So we advice you get in the habit of asking yourself, “Could I do this with a HAVING statement or WHERE statement instead of using a WHILE LOOP END LOOP structure?” In the somewhat rare situations where a HAVING or WHERE clause won’t do the trick, WHILE might be exactly what you need. Let’s look at some use cases.

Use case 1: Simple number counting

-- (1) Declare your variables
DECLARE
i_start INT := 0;
i_increment INT := 1;
i_current INT := 1;
i_end INT := 6;
-- (2) Tell Postgres the scope of our program, nested in a BEGIN..END container.
BEGIN
    -- (3) Define our loop within a LOOP..END LOOP container.
    WHILE i_current <= i_end
        LOOP
            -- (4) Code to be executed. In this case, we are doing two things:
            -- (a) Incrementing our i_current variable.
            i_current := i_current + i_increment;
            -- (b) Printing the value of i_current.
            RAISE NOTICE i_current;
            -- info tidbit: in most other forms of TSQL, we use "Print" instead of "Raise Notice".
        END LOOP;
END;

Analysis of the code above:

  • First we DECLARE the variables we’ll use in our WHILE loop.
  • Next comes a BEGIN statement to delineate the start of our “program”.
  • Then we define our loop type as WHILE, along with it’s condition. As long as this condition evaluates to true, the commands between LOOP and END LOOP will be followed.

Executing the above code will give the following results:

1
2
3
4
5
6

Now that you got your feet wet with a basic example, let’s actually modify a database!

Use case 2: Fill table with 350 consecutive numbers

Now, we will apply our new knowledge of the WHILE loop to a database table. We’ll begin by looking at a simple case where we want to fill a column with consecutive numbers.

First, let’s set up a simple test table with SQL’s CREATE TABLE command:

-- CREATE TABLE creates a new, empty table.
CREATE TABLE public.test (
    -- Telling our new table to have one field named "id_new_num" and being of the "int8" (integer) data type.
    id_new_num int8
);

Info tidbit: Please note: If you do not have autocommit turned on, you may need to issue a COMMIT command. If you have not used COMMIT with ROLLBACK, we highly recommend exploring and learning about these commands!

When we execute the code above in PostgreSQL, we get a fresh, empty table with one field (column). Now we will quickly fill the first 350 rows of the “id_new_num” column with a number we will increment via our WHILE LOOP.

DECLARE
i_start INT := 0;
i_increment INT := 1;
i_current INT := 1;
i_end INT := 350;
BEGIN
    WHILE i_current <= i_end
        LOOP
            i_current := i_current + i_increment;
            -- info tidbit: in most other forms of TSQL, the colon you see above is not necessary.
            INSERT INTO test (id_new_num)
            SELECT i_current;
        END LOOP;
END;

Please note: Again: if you do not have autocommit turned on, you may need to issue a COMMIT command for database changes.

In your modified new code above, the only change we have made is to change “i_end” from 5 to 350 and remove RAISE NOTICE i_current; and replace it with INSERT INTO test (id_new_num) SELECT i_current; so as to add records to a table. Specifically, we filled five rows of the “id_new_num” field with the numbers 1 through 350.

Let’s see the query results!

To be sure we are getting what we want, after we execute that query, we can check to see how many records were created:

SELECT COUNT(*) FROM public.test;

When we execute the above SELECT command, PostgreSQL will return a count of 350 because we added 350 rows to our empty table.

Conclusion

We learned in this article some basic uses of the WHILE looping statement in Postgres SQL queries. We also explored some examples where one may need to use this statement. Given the complexity and breadth of possibilities with WHILE loops and looping in general, we kept this tutorial as simple as possible, while at the same time throwing in some “info tidbits” we encourage you to explore. While this tutorial was written specifically for users of PostgreSQL and not coding language specific, we saw importance in making it clear that the WHILE LOOP structure changes a bit between the different popular relational database systems.

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.