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.