In PostgreSQL, we use CITEXT to store the data that can ignore case sensitivity. This data type is developed, based on the TEXT data type in PostgreSQL and provides an efficient way to compare the text ignoring the case sensitivity. Let’s see the details of CITEXT data type using examples.

PostgreSQL CITEXT Data Type

The CITEXT data type is a case-insensitive data type that allows you to do text comparisons without considering the case an issue. For example, if we want to fetch data from the table, we can get it using a SELECT statement along with a WHERE statement if we need to get data for a specific condition. Let's consider the following table as an example:

Now if we want to get the data of the student named “alex” we will write the following query:

SELECT * FROM students_info WHERE studentname = 'alex';

By executing this query we will see that this query did not give any result.

Whereas the student with the name “Alex” does exist in the database table. Which is not a good thing, we need to figure out the issue. So what's the issue?

The issue is that we have called the name of the student in lowercase letters. This means that the name field has been declared by the data type that is case-sensitive.

The CITEXT data type can rescue us in such conditions. This data type is case-insensitive and it can do data comparison without making the case a big issue. The basic syntax for using the CITEXT data type is:

CREATE TABLE tab_name (
  col_name CITEXT
  );

While creating the table we need to declare the data type of the field as CITEXT. Now let’s fix the problem. Write the following query for the creation of a table and declare the columns as CITEXT:

CREATE TABLE Students_Info
   (
   StudentID int,
   StudentName CITEXT,
   Address CITEXT,
   City CITEXT)
   );

Now if you are using the CITEXT data type for the first time, you may encounter an error that:

To fix this error you need to first create an extension of the data type CITEXT. Let’s do it by executing the following query:

CREATE EXTENSION CITEXT

This query will successfully create an extension for CITEXT. Now if we create the table using the above written code we will be successful in creating it. The next step is to insert the values into the table.

INSERT INTO students_info(studentid, studentname ,address,city)
 VALUES ( 01,'John','13th Street. 47 W 13th St' , 'New York'),
  ( 02,'Alex','24th Street. 32 E 24th St' , 'San Diego'),
  ( 03,'Peter','6th Street. 23 W 6th St' , 'San Francisco')
 RETURNING *;

The table with the values will look like this:

In the above output, we can see the data type of these fields is CITEXT. Now we will see if this has solved the issue or not. We will retrieve the data for the student name “alex”. Execute the following query:

SELECT * FROM students_info WHERE studentname = 'alex';

Following is the output:

We can see that, the query has returned the output even though we have retrieved the data of the student with lowercase letters.

Conclusion

The CITEXT data type is case-insensitive, allowing you to do text comparisons without worrying about the case. In this post, we have talked in detail about how the CITEXT data type assists in data integrity. The case-insensitive nature of the CITEXT data type is used in the comparison of texts without caring about the case.