PostgreSQL Lower Function

When working with any database, you may get a situation where you want to retrieve the data but in a different case such as lowercase. Maybe the values in the database are capitalized or uppercase, but you want the result to be lowercase. For that, you must use a function that lets you change the values to lowercase. For PostgreSQL, the LOWER function does the trick, and using it is straightforward. This post gives examples on how to quickly use the LOWER function in PostgreSQL.

Examples of Using the LOWER Function in PostgreSQL

PostgreSQL offers the LOWER function as one of its string functions. With it, you can quickly convert the uppercase characters in your table to lowercase. When handling the text values in your PostgreSQL database, using this function is handy, but you should note that it only works if the values that you want to convert are already all in uppercase.

Here are a few examples to get you started.

Example 1: Working with a String

Using the LOWER function is easy. With the following syntax, you are good to go:

LOWER(string);

The string in the following syntax is the string that you wish to convert. For instance, we can use the SELECT statement to convert the uppercase string that we provided in the query statement as shown in the following:

Once you execute the statement, it returns the provided string in lowercase.

Example 2: Working with a Table

Even when you have uppercase entries in your table, you can retrieve them in lowercase using the LOWER function. You still use the same syntax, but the select statement will slightly change to accommodate the values that you want to retrieve from your table.

Let’s create the details in the following table that we will use for this example:

Once the table is created, quickly insert the values into the table. We ensured that the string values in the table are all uppercase to help us demonstrate how to use the LOWER function.

Our final table appears as follows. We target the “fname” and “lname” columns:

Suppose we want to retrieve all values in our table but have the ‘lname” values converted into lowercase. We have our command with the LOWER function as follows:

SELECT fname, LOWER(lname), age FROM details;

Note that the LOWER function is only applied to the column that we are targeting. Other columns have their values as they appear in the table.

Example 3: Combining the LOWER Function with Other Clauses

Combining the LOWER function with other functions and clauses to meet your goals is possible. For instance, if you want to use a conditional query with the WHERE clause, you can do so as demonstrated in the following:

The following example shows how we use the WHERE clause to only select the entries whose age is greater than 25. We add the LOWER function to retrieve the “fname” values as lowercase values.

In the output, you can use the AS statement to give it a different name for convenience. Here’s an example to demonstrate how we name the lowercase column with a different name.

Conclusion

The PostgreSQL LOWER function is handy when you have a column with uppercase values and you want to retrieve them in lowercase. The syntax to use this function is straightforward. When you add it to your SELECT query or other statement in your PostgreSQL, you will quickly get the desired output. The examples presented in this post are clear. Once you attempt them for your case, you will quickly master how to work with the LOWER function in PostgreSQL.