Citext

The citext extension in PostgreSQL provides a case-insensitive text type. It behaves just like the standard TEXT data type but treats values as case-insensitive when comparing or indexing, making it useful for case-insensitive searches and unique constraints. Your Nile database arrives with citext extension already enabled, so there’s no need to run create extension.

Creating and Populating a Sample Table

Before creating the index, let’s create a sample table and populate it with data:

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    column1 CITEXT,
    column2 INT
);

INSERT INTO my_table (column1, column2) VALUES
    ('Apple', 10),
    ('banana', 20),
    ('GRAPE', 15),
    ('Orange', 25),
    ('Lemon', 30);

Benefits of Using citext

Allows case-insensitive text comparisons without using LOWER().
Simplifies case-insensitive unique constraints and indexes.
Reduces errors when working with user-provided text data like emails or usernames.

Creating an Index on citext Columns

A B-tree index can be created on a CITEXT column just like a TEXT column:

CREATE INDEX citext_idx ON my_table (column1);

This index will allow efficient case-insensitive lookups.

Enforcing Unique Constraints with citext

A UNIQUE constraint on a CITEXT column ensures case-insensitive uniqueness:

ALTER TABLE my_table ADD CONSTRAINT unique_column1 UNIQUE (column1);

Now, inserting values like 'APPLE' or 'apple' would result in a constraint violation.

Querying with citext

Once the citext extension is enabled, queries automatically become case-insensitive:

SELECT * FROM my_table WHERE column1 = 'aPpLe';

This will return the same result as searching for 'Apple', 'APPLE', or 'apple'.

Limitations

CITEXT is slightly slower than TEXT due to case normalization.
It does not support LIKE queries efficiently unless you create a functional index using LOWER(column1).
Collation-sensitive operations may not always behave as expected.

Removing a citext Index

If you need to remove an index:

DROP INDEX citext_idx;

Conclusion

The citext extension in PostgreSQL simplifies case-insensitive text handling, making it ideal for usernames, emails, and other text fields where case differences should be ignored. For more details, refer to the PostgreSQL documentation.

Get Started

Tenant Virtualization

Postgres Concepts

Extensions

Creating and Populating a Sample Table

Benefits of Using citext

Creating an Index on citext Columns

Enforcing Unique Constraints with citext

Querying with citext

Limitations

Removing a citext Index

Conclusion

​Creating and Populating a Sample Table

​Benefits of Using citext

​Creating an Index on citext Columns

​Enforcing Unique Constraints with citext

​Querying with citext

​Limitations

​Removing a citext Index

​Conclusion

Creating and Populating a Sample Table

Benefits of Using citext

Creating an Index on citext Columns

Enforcing Unique Constraints with citext

Querying with citext

Limitations

Removing a citext Index

Conclusion