While Loop TimescaleDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this lesson, we will learn to enhance the power of our database queries using the While loop in TimescaleDB. This lesson will include but not be limited to:

  • What? What does “WHILE” do and what’s 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

  • Understanding of how to execute queries statements, either with TimescaleDB (or Postgres, MS SQL Server, MySQL, etc.) alone, using Dbeaver or some other database administration tool, or even by using a scripting language (like Python, Node, PHP, Java, etc.) that supplies a connection to the database as well as a method for sending it SQL (or T-SQL) commands, to make changes to a database.
  • Novice level grasping of basic SQL commands, including SELECT, FROM, and INSERT.

The first thing to do is set up a database instance. We prefer using Objectrocket because we like their value and efficiency.

Create a TimescaleDB Database Instance on ObjectRocket

  • (1) Go to kb.objectrocket.com and “Log In”.
  • (2) Use left menu to “Create Instance”.
  • (3) In the “Name” field, add a descriptive name for your instance.
  • (4) Under “Select your service” choose the system you want to use from “CockroachDB, Elasticsearch, PostgreSQL, Redis, TimescaleDB, and MongoDB”.
  • (5) Choose the “Cloud Provider” and “Type” you want.
  • (6) Select “Version” and “Region”.
  • (7) Click the “GO TO STEP 2” button.
  • (8) Make a choice in the “Pick Your Flavor” section.
  • (9) Choose the “Capacity (Storage/Memory)”. Notice how this choice influences your total fee at the bottom right.
  • (10) Click the “GO TO STEP 3” button.
  • (11) Under “Add a Whitelist IP” pick “ALOW ANY IP” or “USE MY IP” or “Add my IPs later”.
  • (12) Choose between “Master” and “Replica” and click the “ADD” button.
  • (13) Now click the “CREATE INSTANCE” button at the bottom.

Before we start building queries, let’s look at how to use Flask’s psycopg2 to connect to a TimescaleDB instance at ObjectRocket.

Connect to TimescaleDB

1
2
3
4
5
6
7
8
9
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

What’s the WHILE Loop?

In TimescaleDB, the WHILE loop structure involves a condition and loop, much like a FOR loop.

Syntax of the While Loop

While loop syntax looks like this:

1
2
3
4
WHILE [condition]
    LOOP
        [statements TO run]
    END LOOP

Note: In some other database systems, including Microsoft’s MS SQL Server, the syntax of a WHILE BEGIN END loop is as follows:

1
2
3
4
WHILE [condition]
    BEGIN
        [statements TO run]
    END

Note: In MySQL, the WHILE LOOP syntax is:

1
2
3
4
WHILE [condition]
    DO
        [statements TO run]
    END WHILE

How to Use WHILE in SQL

It is important to consider that while creating an SQL statement, a WHERE or HAVING clause will usually be sufficient for limiting which rows are returned or changed. Sometimes, a WHILE LOOP will add extra complexity to your code or SQL. So we advise you to get in the habit of thinking to yourself, “Can I attain my task with a HAVING statement or WHERE statement instead of using a WHILE LOOP?” In the rare circumstances where a HAVING or WHERE clause will not suffice for your purposes, a WHILE loop might be what’s best to use. Let us now study a few use cases.

Number Counting With WHILE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Declare vars
DECLARE
intStart OUT INT := 0;
intIncrement INT := 2;
intCurrent INT := 2;
intEndAt INT := 12;
-- Inform TimescaleDB of your program scope, within a BEGIN container.
BEGIN
    --  Define our nested LOOP..END LOOP container.
    WHILE intCurrent <= intEndAt
        LOOP
            -- Code to be run. Here we're doing two things:
            -- (1) Increment our i_current variable.
            intCurrent := intCurrent + intIncrement;
            -- (2) Printing the value of intCurrent.
            RAISE NOTICE intCurrent;
            -- Misc information: in some other forms of SQL, we use "Print" instead of "Raise Notice".
        END LOOP;
END;

Analysis

  • First we DECLARE our program variables to use in our TimescaleDB WHILE loop, such as intStart, intIncrement, intCurrent, and intEndAt. Hopefully, these variable names tell you all you want to understand about their purpose.
  • Next we placed a BEGIN statement to delineate the starting point point of our TimescaleDB program.
  • Finally, we define our loop type as WHILE, along with its condition for exiting the loop. As long as this condition evaluates to true, the commands between LOOP and END LOOP will be executed.

Executing the above code using dBeaver, Python, or our console will give the following results:

1
2
3
4
5
6
2
4
6
8
10
12

Now that you have played with an example of a basic use of the WHILE loop for TimescaleDB, we shall modify a TimescaleDB database’s rows.

Fill Table With Consecutive Numbers

Now, we’ll use our basic grasping of the WHILE loop to add data to a TimescaleDB table. We will begin by examining a case where we need to fill records with a set of numbers.

First, we will set up a table named “tblTest” using TimescaleDB’s CREATE TABLE statement:

1
2
3
4
5
-- CREATE TABLE establishes a new table, which has no data in it yet.
CREATE TABLE tblTest (
    -- Instructs the new table we want it to have one column called "idNewNum", which is of the "int8" (integer) data type.
    idNewNum int8
);

Important information: If you do not have autocommit turned on in your TimescaleDB settings, you may want to issue a COMMIT command. If you have not used COMMIT with ROLLBACK, we recommend you investigate and study these statements.

When we execute the SQL above against our TimescaleDB database, we get a new, empty table with one column, idNewNum. Next we’ll easily fill the first 260 records of the “idNewNum” column with a number we’ll increment with a WHILE LOOP.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
intStart OUT INT := 0;
intIncrement INT := 1;
intCurrent INT := 1;
intEnd INT := 260;
BEGIN
    WHILE intCurrent <= intEnd
        LOOP
            intCurrent := intCurrent + intIncrement;
            -- Misc note: in some other forms of SQL, the ":" you see above is not needed.
            INSERT INTO tblTest (idNewNum)
            SELECT intCurrent;
        END LOOP;
END;

Important thing to remember (yes, again): if you do not have autocommit turned on, you may want to issue a COMMIT command for any changes to our database.

In your new, modified SQL above, the only change we made is to change “intEnd” from 12 to 260, “intIncrement” from 2 to 1, and remove RAISE NOTICE intCurrent, replacing it with INSERT INTO test (idNewNum) SELECT intCurrent; so to insert rows into the tblTest TimescaleDB table. Specifically, we filled 260 records of the “idNewNum” column with the numbers 1 through 260.

WHILE Loop SQL Results

To make sure we’re retrieving the results we expected, after we execute that query, we can check to see how many records were created with the following SQL:

1
SELECT COUNT(*) FROM tblTest;

When we execute the above SQL command, TimescaleDB will return a count of 260 because we added 260 records to our previously empty tblTest table.

Conclusion

In this document we see some uses of WHILE looping in TimescaleDB SQL queries. We also investigated some examples where one may want to use this type of looping construct as opposed to something like a FOR loop. With the potential complexity and number of possibilities for WHILE and other loops, we strove to keep this lesson as simple as possible, while threcording in some related subtopics you’re encouraged to investigate. While this lesson was built specifically for users of TimescaleDB and not specific to any scripting language, we saw justification for making it clear that the WHILE LOOP structure changes some between the different popular relational database systems in use today.

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.