How to Search Data Saved in Uppercase in PostgreSQL

1. Creating a Database
2. Connecting to case_database Database
3. Populating case_databasewith Data
4. Create a New Node.js Application
5. the Erroneous Query in Action
6. the Solution to the Erroneous Query

Most applications have a search bar for a user to search for a specific product or service offered by an application. The search bar uses keywords to query a database to return a product or service that meets the search criteria.

For example, in an e-commerce application, we can use the product’s name as the keyword to search for a product with that name.

However, a common pitfall when using keywords is that the products might not be returned when the keyword is used in lowercase form while the keyword in the database exists in uppercase form.

This tutorial shows how to use a keyword written in lowercase to search for data saved using uppercase in a PostgreSQL database.

Creating a Database

Use the keyboard shortcut CTRL+ALT+T to open a new terminal window. Use the command given below to log in to the PostgreSQL database.

~$ psql -U postgres -h localhost

Enter the password for the PostgreSQL server on the password prompt and press the Enter from your keyboard. After successfully logging in, the terminal window should look as shown below.

Password for user postgres: 
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Note that we are connected to the database named postgres. To create a database for this example, copy and paste the following window into the terminal window and press the Enter from your keyboard.

postgres=# create database case_database;
CREATE DATABASE

The CREATE DATABASE shows that we have created the database successfully. See the following section to learn how to connect to our new database.

Connecting to case_database Database

Use the following command to connect to case_database.

postgres=# \c case_database 
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "case_database" as user "postgres".

Populating case_databasewith Data

To achieve this, we will create a table named employee and populate it with some values we will use in our examples. Next, copy & paste the below code into the terminal window and press the Enter from your keyboard.

case_database=# CREATE TABLE employee(
case_database(# id SERIAL UNIQUE NOT NULL,
case_database(# firstname VARCHAR(50) NOT NULL,
case_database(# lastname VARCHAR(50) NOT NULL,
case_database(# email VARCHAR(100) NOT NULL);
CREATE TABLE

case_database=# INSERT INTO employee(firstname, lastname, email)
case_database-# VALUES
case_database-# ('john', 'doe', 'john@gmail.com'),
case_database-# ('MARY', 'public', 'mary@gmail.com'),
case_database-# ('ELON', 'MUSK', 'elon@gmail.com');
INSERT 0 3

Create a New Node.js Application

Open WebStorm IDEA and select File -> New -> Project. On the window that opens, change the project name from untitled to postgresql-lowercase on the Location section or use any name you prefer.

Ensure you’ve installed the node runtime environment so that the Node interpreter and Package manager sections can be added automatically. Finally, hit the Create button to generate the project.

Once the project has been generated, create a file named config.js under the current folder. After that, copy & paste the following code into that file.

import postgres from 'postgres';
const connection = postgres({
  user: 'postgres',
  host: 'localhost',
  database: 'case_database',
  password: 'postgres',
  port: 5432
})
export default connection;

In this file, we have added the configuration details that will help us to connect to our PostgreSQL database named case_database.

Since we are working with modules, we need to add the following JSON property to the file named package.json.

{
  "type": "module",
}

the Erroneous Query in Action

Create a file named query-issue.js under the current folder and copy and paste the following code into that file.

import connection from './config.js';

async function findEmployeeByFirstName(firstName) {
  return connection
  `SELECT * FROM employee 
         WHERE firstName = ${firstName}`;
}
findEmployeeByFirstName('mary').then(employee => {console.log(employee)})

In this file, we have created an async function named findEmployeeByFirstName() that accepts a single parameter named firstName.

The method is self-explanatory. However, remember that the method uses the firstName provided to search for an employee that matches this name.

We have added an async keyword to the function because the method returns a Promise. This means that the method is executed asynchronously. We can use the then() method on the returned promise to further process the returned data.

Note that we’ve passed mary as the method’s argument, which is lowercase, but a record in our database is saved in uppercase.

When we execute this method, our query does not return any value even though the names are similar, and it is an issue associated with using data with different cases. Use the following command to execute this file.

~/WebstormProjects/postgresql-lowercase$ node query-issue.js 

The following is the output returned after executing this file.

Result(0) []

the Solution to the Erroneous Query

Create a file named query-solution.js under the current folder and copy and paste the following code into that file.

import connection from './config.js';

async function findEmployeeByFirstName(firstName) {
  return connection
  `SELECT * FROM employee 
         WHERE firstName ILIKE ${firstName}`;
}
findEmployeeByFirstName('mary').then(employee => console.log(employee));

This code fence is similar to the previous one. The only change we have made is adding ILIKE to the query after the WHERE clause.

The ILIKE clause is similar to the LIKE clause from SQL, which is used for pattern matching. The only difference between LIKE and ILIKE is that the ILIKE clause is case insensitive.

Since the ILIKE clause is case insensitive, we can now return the record that matches our search keyword, which in our case is mary. Use the following command to execute this file.

~/WebstormProjects/postgresql-lowercase$ node query-solution.js 

The following is the output returned after executing this file.

Result(1) [
  {
    id: 2,
    firstname: 'MARY',
    lastname: 'public',
    email: 'mary@gmail.com'
  }
]

So, we have learned how to use a keyword written in lowercase to search for data saved using uppercase in PostgreSQL.

To realize this, we have used the ILIKE clause, which is used for pattern matching regardless of the case used to save the data. We can also use a keyword in uppercase to search for a record kept in lowercase.