Postgres Case-Insensitive

June 25, 2023 | by Arround The Web | No comments

String comparison is a prevalent feature in databases. It allows us to compare a given substring with another string. This is useful for pattern matching.

However, like all languages, pattern matching is case-sensitive by default. Hence, when performing string comparison in the PostgreSQL databases, you need to take into account the character casing.

In some cases, we may need to perform case-insensitive string comparisons. For example, we can implement case-insensitivity for a database or column in PostgreSQL using various methods and techniques.

This tutorial explores how you can implement case insensitivity in the PostgreSQL databases.

Method 1: Using the Citext Data Type

The first tool that you can use to get case-insensitive support in the PostgreSQL database is the citext data type which is provided by the citext extension.

The first step is to install the citext extension on the server. You can install it by running the following query:

CREATE EXTENSION IF NOT EXISTS citext;

Once the extension is installed, you can create a column with the citext data type which allows you to perform the case-insensitive comparisons.

For example, we can create a column with the specified type:

CREATE TABLE my_table (
my_col citext
);

Once you enabled the support for citext data types, you can perform the case-insensitive comparison using the standard SQL operators such as = or LIKE.

SELECT * FROM my_table WHERE my_column = 'example';

Method 2: Changing the Collation

Another method of enabling the case-insensitive comparisons in PostgreSQL is changing the collation. PostgreSQL allows us to change a database, table, or column collation.

We can use this feature to change the collation of a given column to a case-insensitive collation as shown in the following example:

ALTER TABLE <table_name>
ALTER COLUMN <column_name>
SET DATA TYPE text COLLATE "C";

This should change the collation of the specified column to a case-insensitive collation.

NOTE: Changing the collation of a given column may have performance implications, especially on large tables.

To perform a case-insensitive comparison in a collation-modified column, run the following command:

SELECT * FROM table_name WHERE column-name = 'example' COLLATE "C";

This should tell SQL that you wish to use the collation that is defined in that column.

Conclusion

We explored two main ways of performing case-insensitive searches in PostgreSQL tables.

Source: linuxhint.com