Case in TimescaleDB

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

Introduction

In this document, we’ll explain a specific type of branching using Case in TimescaleDB, including:

  • What? What does the “CASE” statement do?
  • Why? In what circumstances will we gain from use of case branching?
  • Where and how? How can we use the “CASE” branch and where/when can we use it in the SQL we write?

Prerequisites

  • Novice experience with building SQL scripts for a TimescaleDB database, using Dbeaver or some other database admin tool, or by using something like .Net or Python that provide a way to connect to and query your TimescaleDB database with SQL commands. Note that TimescaleDB functionality is new to Dbeaver.
  • Novice level knowledge of how the “SELECT”, “FROM”, and “WHERE” statements work in TimescaleDB queries.

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 “CASE”?

In TimescaleDB, “CASE” interprets one or more conditions and returns results based on the outcomes of those conditions. Depending on your situation, it can be more efficient than using the “IF…ELSE” branch type. One difference is that “IF…ELSE” statements will control the order of execution and “CASE” does not.

Why use “CASE” in queries

Use case: Return value based on condition

Let us start by examining a use case where we need to query our company “tblUsers” and return an honorific along with each user’s row, using case to check gender. In other words, if their gender is “female” then we want an honorific of “Ms.” returned and if their gender is “male” we want “Mr.” to be returned.

Run the following SQL statement in your TimescaleDB console, dBeaver, or in Python, if that is your preference:

1
2
3
4
5
6
SELECT
txtNameFirst
, txtNameLast
, txtGender
, numAge
FROM tblUsers

You’ll see the following rows returned:

txtNameFirsttxtNameLasttxtGendernumAge
FrannyTranfemale44
BobbyJimsonmale39
GabbySkankersonmale51
GinjerPebblesonunknown38
GandalphGreymale572
BennieTuckormale45

Next we’ll use the “CASE” statement to return a new column called txtHonorific. Notice that this column does not exist in our “tblUsers” table; we have used “CASE” to create this column (field) dynamically. It’s important to note here that here when we say “create,” we mean the “column” and values in it are returned in the rowset returned by our SQL.

1
2
3
4
5
6
7
8
9
10
SELECT
txtNameFirst
, txtNameLast
, txtGender
, CASE
      WHEN txtGender = 'male' THEN 'Mr.'
      WHEN txtGender = 'female' THEN 'Ms.'
  END AS txtHonorific
, numAge
FROM tblUsers

Notice how similar CASE is to IF ELSE?

txtNameFirsttxtNameLasttxtGendertxtHonorificnumAge
FrannyTranfemaleMs.44
BobbyJimsonmaleMr.39
GabbySkankersonmaleMr.51
GinjerPebblesonunknown38
GandalphGreymaleMr.572
BennieTuckormaleMr.45

Notice a new column – “txtHonorific” – is returned in our database output that’s not a column in our “tblUsers” table? Now you probably see how one of our users, Ginjer Pebbleson, has “ununderstandn” in the “txtGender” column and you may be asking, “What if there are some other genders in the ‘tblUsers’ table other than ‘male’ or ‘female’?” Fortunately, just like the “IF” statement, “CASE” supplies us with “ELSE”.

1
2
3
4
5
6
7
8
9
10
11
SELECT
txtNameFirst
, txtNameLast
, txtGender
,   CASE
        WHEN txtGender = 'male' THEN 'Mr.'
        WHEN txtGender = 'female' THEN 'Ms.'
        ELSE 'Zee'
    END AS txtHonorific
, numAge
FROM tblUsers
txtNameFirsttxtNameLasttxtGendertxtHonorificnumAge
FrannyTranfemaleMs.34
BobbyJimsonmaleMr.29
GabbySkankersonmaleMr.41
GinjerPebblesonZee.28
GandalphGreymaleMr.562
BennieTuckormaleMr.35

Study the change we made to this TimescaleDB query, adding “ELSE ‘Zee'”, returns “Zee” as txtHonorific when their txtGender value is anything but “male” or “female”. Note: our “ELSE” also accounts for any NULL values found in the “txtGender” column.

Case execution order

An important item to consider when using Case with TimescaleDB: The entirety of a “CASE…END” statement is not evaluated in order; the entirety of it is evaluated as a batch. Here’s how that happens: We’ll take the last query we created above and add one more “WHEN” clause to the starting point of our CASE:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
txtNameFirst
, txtNameLast
, txtGender
, CASE
    WHEN numAge > 201 THEN 'Immortal'
    WHEN txtGender = 'male' THEN 'Mr.'
    WHEN txtGender = 'female' THEN 'Ms.'
    ELSE 'Zee.'
  END AS txtHonorific
, numAge
FROM tblUsers

So now if we run this query, we’ll see the following results:

txtNameFirsttxtNameLasttxtGendertxtHonorificnumAge
FrannyTranfemaleMs.44
BobbyJimsonmaleMr.39
GabbySkankersonmaleMr.51
GinjerPebblesonZee.38
GandalphGreymaleImmortal572
BennieTuckormaleMr.45

What’s important about the above TimescaleDB printout? If our CASE statement had executed each WHEN clause one at a time, Gandalf would first be evaluated by his age with his honorific being set to “Immortal” and then he would be evaluated based on his gender and the honorific changed to be “Mr.”. But that’s not the case here. What occurred is when one condition was met, i.e., that first one, the value is set for now on, and the SQL interpreter moves on.

How and Where to use CASE?

Here we used “CASE” as piece of the “SELECT” clause in our TimescaleDB database. Just like IF ELSE, CASE can be used with UPDATE, DELETE, WHERE, and HAVING. CASE statements can even be nested! We’ll save that situation for a future document.

Conclusion

We seed in this document how to use the “CASE” statement in TimescaleDB database SQL. We also investigated a real business example where one may need to use the CASE WHEN branch, perhaps because you want an all-at-once evaluation, rather than the sequential type evaluation that IF ELSE branching supplies.

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.