Introduction to PostgreSQL Case Insensitive
PostgreSQL case insensitive is defined as searching with considering as the SQL select queries and the regular expression in PostgreSQL. While using regular expressions, we need to use the PostgreSQL ~* operator instead of the like operator; we can also use the ilike operator in PostgreSQL. We can also create an extension name as citext to use the case insensitive query in PostgreSQL; we need to create it first to use the extension of citext. We have also used the lower function in PostgreSQL to use the case insensitive query; we also have to add an index of lower function on the column.
Syntax
Different syntax are mentioned below:
1. PostgreSQL case insensitive by using the citext extension
Create extension citext (Create new extension of citext in PostgreSQL);
Select name_of_column1, name_of_column2, name_of_column3, …, name_of_columnN from name_of_table where name_of_column::citext IN (value_of_column1, value_of_column2, value_of_column3, …, value_of_columnN);
2. PostgreSQL case insensitive using the lower function
SELECT name_of_column1, name_of_column2, name_of_column3, …, name_of_columnN from name_of_table WHERE lower('name_of_column') = LOWER('name_of_column');
3. PostgreSQL case insensitive using add the index of lower function
Create index name_of_index on name_of_table (lower (name_of_column));
Below is the parameter description syntax of PostgreSQL case insensitive.
- Select –This operation is defined as select the column rows from using doesn’t care about casting. We have to use the citext extension to retrieve records from the table.
- Create extension – This is defined as creating an extension name as citext in PostgreSQL. So, for example, we can create this extension to define the case insensitive.
- Citext – This is the extension used in PostgreSQL case insensitive. If we have to fetch records from the string table without casting, then we have to use the citext extension in PostgreSQL.
- Name of column 1 to name of column N – This is defined as a select number of columns from the table to use the lower function and citext extension inquery of case insensitive in PostgreSQL.
- Name of the table – This is defined as the name of the table to select the number of columns to use the lower function and citext extension inquery of case insensitive in PostgreSQL.
- Where condition – We are using the where condition in PostgreSQL to retrieve the specified records from the table. We have used where condition to using citext extension in PostgreSQL.
- Lower – This function is used in the query of case insensitive. While using the lower function, it will not issue the error of typecasting.
- Create index – We are creating an index on the column while using the lower function. This is defined as to create the index by using the lower function in PostgreSQL.
- Name of the index – This is defined as creating a new index by using the lower function in PostgreSQL.
How Case Insensitive Query work in PostgreSQL
- Below is the working of case insensitive query in PostgreSQL.
- Now, we are using citext extension to work query of case insensitive in PostgreSQL. By default, PostgreSQL has not installed the citext extension; we need to first install the same on our server to use this extension.
- The below example shows that by default citext extension is not installed; we need to install first to use the same in PostgreSQL.
select * from stud2 where name::citext in ('ABC', 'PQR');
create extension citext;
select * from stud2 where name::citext in ('ABC', 'PQR');
- In this example, we have created an extension of citext by using the command of creating an extension, and the extension name is citext.
- After creating an extension, the select operation is successful while using the citext extension.
- Citext extension will call lower function internally into the system. Citext extension is called the lower function automatically. Therefore, we do not need to use the lower function in our query while using the citext extension in PostgreSQL.
- To check citext extension is enabled or not, we are using the following command as follows.
\dx citext;
- Using the above command, we can check the citext extension is installed or not on our PostgreSQL database server. Also, we can check the version name, schema name on which we have enabled the extension.
- It will also show the description of the citext extension.
Examples
Below is the example of case insensitive in PostgreSQL is as follows. We are using the case_test table to describe the example of case insensitive.
Below is the structure and data for the table of the case_test.
\d+ case_test;
select * from case_test;
1. Case insensitive query by using citext extension –
- The below example shows that case is insensitive by using the citext extension.
- We have used ‘ABC,’ ‘PQR,’ ‘aBc,’ ‘PQr’ string to search the data from the case_test table.
select * from case_test where name::citext in ('ABC', 'PQR', 'aBc', 'PQr');
2. Case insensitive query by using lower function –
- The below example shows that case is insensitive by using the lower function.
- We have used a lower function on the name column to fetch the data from the case_test table.
SELECT * FROM case_test WHERE lower('Name') = LOWER('name');
3. Case insensitive query by creating a lower function index on the column
The below example shows that case insensitive query by creating a lower function index on the column.
Create index lower_idx on case_test (lower(name));
SELECT * FROM case_test WHERE name = 'ABC';
Recommended Articles
This is a guide to PostgreSQL Case Insensitive. Here we discuss How Case Insensitive Query works in PostgreSQL along with the Examples. You may also have a look at the following articles to learn more –
