Change Case in TimescaleDB

Introduction

In this document, we’ll talk about how to change case in TimescaleDB using the UPPER and LOWER functions in SQL to change characters from three points of view, including:

• What? What do the UPPER and LOWER functions do for us in relation to text?
• How? How do we use these text manipulation functions in TimescaleDB SQL scripts?
• Why? In what circumstances do we need these two functions in? We have included some use cases to aid in knowledge.

Prerequisites

High level understanding of how to write queries for TimescaleDB (or Postgres, MS SQL Server, etc.), using the free Dbeaver tool or some other administration tool that works for TimescaleDB, or even by using a server-side scripting language like Python, PHP, C#, VB.Net, ASP.Net, Java, Node.js, etc.) that allows control of the database as well as a way to send it SQL commands to view or make changes to your data.

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

UPPER and LOWER Functions

TimescaleDBs LOWER function returns a lowercase string of any text submitted to the function as the one parameter this function accepts. So if you give the function something like, “I LOVE KALE,” the LOWER() function will return “i love kale”. The UPPER function returns a uppercase string of any text submitted to the function. So if you seed the function with “i use python daily,” the UPPER() function will return “I USE PYTHON DAILY.”

Using Upper and Lower Functions

Upper and Lower Syntax

Let us now study an example of how the LOWER function works. In this situation, we want consistent results returned from the database in terms of case so that we can do comparisons between returned values. In other words, if a user searches for “PostgreSQL,” – and they did not pick the “match case” checkbox – we prefer the search results to return both “Postgres” and “PostgreSQL”, whatever case the user typed into the search box.

Here is a table we have created and filled with data to use for this demonstration:

The above SQL will return the following results:

Now we’ll do the same with the UPPER function:

Returns the following records from the TimescaleDB database:

Why Use LOWER and UPPER in TimescaleDB?

UPPER and LOWER in Use Case

One use case would be if we wish to modify a string or strings and we’re unsure of what capitalization they may already have and we wish to combine the technology name field value with technology category, to be one part of text (word) and we wish complete consistency in how we receive this data. First, let us examine our full table of information:

Study the first record of data returned. There we have “Postgres” and “RDB” in the txtName and txtCategory columns. If we query our database like so:

We get the following back from TimescaleDB:

But we want consistency in the case of the text returned, so we use the UPPER() function to shift all text to upper-case:

Which looks like:

What if we want the consistency to be in lower case instead of upper case? To attain this, we change the SQL to be:

Which returns the following records of data from the TimescaleDB database:

Now for some concatenation, so we can show you a very practical way these functions can be used in “business reality” use cases. I say “some concatenation,” because that’s a lesson of its own and I need to keep this tutorial document simple and to the point. The same applies to the RIGHT(), LEFT(), and LEN() functions used in some of the examples in this document. Notice the “||” operator we introduce in the next block of SQL. Before we examine that, a small, brief example of the use of the concatenation operator:

The above SQL yields: “Hello World!”.

Concatenation in TimescaleDB

First, let’s freshen our memory of the unchanged contents of our “tblTechnologies” table:

Next we’ll create the following TimescaleDB SQL query view:

The above SQL in TimescaleDB returns the following rows:

Initcaps for TimescaleDB

We can simplify the above case by using the INITCAP() function. Why didn’t we use Initcap() before? Because those examples help us grasp how to use LOWER and UPPER, which will at times be useful.

Finally, a quick examination of another way to do concatenation, using TimescaleDB’s CONCAT() function:

Miscellaneous:

You may need to investigate our other lessons on LEFT, RIGHT, and a deeper dive into string concatenation for TimescaleDB.

Conclusion

In this instructional article we saw how to use the LOWER and UPPER functions to change case of text values in TimescaleDB. We also investigated some real business examples. Finally, we quickly looked at some related functions such as LEN, LEFT, RIGHT, and CONCAT, as well as the shorthand for the CONCAT function.