In PostgreSQL, various inbuilt string functions are available to perform different functionalities on the strings. For instance, the CONCAT() function concatenates various strings, the REPLACE() function replaces a substring with some other string, and so on.

Similarly, letter case conversion from lower to upper or upper to lower is a very common task while working with strings. To deal with such cases more effectively, Postgres offers some built-in functions such as LOWER(), UPPER(), and INITCAP().

In this blog post, we will discuss the working of the LOWER() function through practical examples.

How to Use LOWER() Function in Postgres?

Postgres' LOWER() function accepts a string as an argument and converts the string’s case to lower. To use the LOWER() function in Postgres, users must follow the below syntax:

LOWER(input_string);

Here, input_string represents a string to be converted into lowercase. The data type of the input_string can be CHAR, VARCHAR, or TEXT.

Let’s comprehend the working of the LOWER() function through practical examples.

Example 1: How to Convert a String Into Lower Case Letters?

Let’s pass the string “WELCOME TO COMMANDPROMPT.COM” to the LOWER() function and see how it works:

SELECT LOWER('WELCOME TO COMMANDPROMPT.COM');

The output shows that the given string has been converted into a lowercase letter successfully.

Example 2: How to Use LOWER() Function on Table’s Data?

Firstly, we will create a sample table named employee_bio with three columns: employee_id, employee_name, and employee_email:

CREATE TABLE employee_bio(
employee_id INT,
employee_name TEXT,
employee_email VARCHAR
);

A table named “employee_bio” with the desired column has been created successfully. To insert data into the “employee_bio” table, we will utilize the following command:

INSERT INTO employee_bio(employee_id, employee_name, employee_email)
VALUES (1, 'JOSEPH', 'JOSEPH@XYZ.COM'),
(2, 'JOHNSON', 'JOHNSON@XYZ.COM'),
(3, 'HENRY', 'HENRY@XYZ.COM');

Suppose we have to convert the email address to lowercase letters; for that purpose, we will utilize the LOWER() function as follows:

SELECT employee_name, employee_email, LOWER(employee_email)
FROM employee_bio;

The output clearly states that the “employee_email” column has been converted into lowercase letters successfully.

Example 3: How to Use LOWER() Function with WHERE Clause in Postgres?

You can use the LOWER() function with the WHERE clause to convert some specific strings into lowercase in Postgres:

SELECT employee_name, employee_email, LOWER(employee_email)
FROM employee_bio
WHERE employee_id = 2;

The output clarifies that this time the LOWER() function converts only that email into lowercase which satisfies the given criteria.

Conclusion

PostgreSQL provides a built-in string function named LOWER() that accepts a string as an argument and converts it into lowercase. It can accept any character string, such as CHAR, TEXT, and VARCHAR. This blog post demonstrated various use cases of the Postgres LOWER() function through practical examples.