Change Case in TimescaleDB

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

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

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()

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

1
2
3
4
5
-- LOWER
txtNewString = LOWER(txtOldString);

-- UPPER
txtNewString = UPPER(txtOldString);

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:

idTechtxtNametxtCategory
0PostgresRDB
1MongoNoSQL
2TimescaleDBRDB
3PythonLanguage
4PHPLanguage
1
SELECT LOWER(txtName) FROM tblTechnologies

The above SQL will return the following results:

txtName
PostgreSQL
mongo
timescaledb
python
php

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

1
SELECT UPPER(txtName) FROM tblTechnologies

Returns the following records from the TimescaleDB database:

txtName
POSTGRES
MONGO
TIMESCALEDB
PYTHON
PHP

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:

idTechtxtNametxtCategory
0PostgresRDB
1MongoNoSQL
2TimescaleDBRDB
3PythonLanguage
4PHPLanguage

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:

1
2
3
4
SELECT
  txtName
, txtCategory
FROM tblTechnologies

We get the following back from TimescaleDB:

txtName
Postgres
Mongo
TimescaleDB
Python
PHP

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

1
2
3
4
SELECT
  UPPER(txtName) -- this returns the uppercase version of txtName
, UPPER(txtCategory) -- this returns the uppercase version of txtCategory
FROM tblTechnologies

Which looks like:

txtNametxtCategory
POSTGRESRDB
MONGONOSQL
COCKROACHDBRDB
PYTHONLANGUAGE
PHPLANGUAGE

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

1
2
3
4
SELECT
  LOWER(txtName)
, LOWER(txtCategory)
FROM tblTechnologies

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

txtNametxtCategory
oraclerdb
mongonosql
cockroachdbrdb
pythonlanguage
phplanguage

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:

1
SELECT 'Hello ' || 'World!';

The above SQL yields: “Hello World!”.

Concatenation in TimescaleDB

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

idTechtxtNametxtCategory
0PostgresRDB
1MongoNoSQL
3TimescaleDBRDB
4PythonLanguage
5PHPLanguage

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
  UPPER(LEFT(txtName,1))
  || LOWER(RIGHT(txtName,LEN(txtName)-1))
  -- LEN(item) returns the length of our string, which is the value of txtName.
  -- RIGHT(item, length) returns the number of characters of item, start outing from the right of the text.
  || ' - '
  -- The '||' symbol concatenates. Here we add a space, then dash, then another space to the value we're building.
  || UPPER(LEFT(txtCategory,1))
  || LOWER(RIGHT(txtCategory,LEN(txtCategory)-1))
  -- Here we've done the same thing to txtCategory that we did to txtName above.
  AS txtCombined
FROM tblTechnologies

The above SQL in TimescaleDB returns the following rows:

txtCombined
Postgres – Rdb
Mongo – Nosql
TimescaleDBdb – Rdb
Python – Language
Php – Language

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.

1
2
3
4
5
SELECT
  INITCAP(txtName)
  || ' - '
  || INITCAP(txtCategory)
FROM tblTechnologies

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

1
2
3
4
5
6
7
SELECT
  CONCAT (
  INITCAP(txtName)
  , ' - '
  , INITCAP(txtCategory)
  ) AS txtName_tech_combined
FROM tblTechnologies

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.

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.