How to Use citext Extension in PostgreSQL for Case-Insensitive Text

To use the citext extension in PostgreSQL, first enable it with CREATE EXTENSION IF NOT EXISTS citext;. Then, use the citext data type for columns where you want case-insensitive text comparisons automatically.

Syntax

The citext extension provides a new data type called citext that behaves like text but ignores case in comparisons.

To enable it, run:

• CREATE EXTENSION IF NOT EXISTS citext; — activates the extension in your database.
• Use citext as a column type in CREATE TABLE or ALTER TABLE.

sql

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username CITEXT UNIQUE
);

Example

This example shows how to create a table with a citext column and how case-insensitive comparisons work automatically.

sql

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username CITEXT UNIQUE
);

INSERT INTO users (username) VALUES ('Alice');

-- This query finds the user regardless of case
SELECT * FROM users WHERE username = 'alice';

Output

 id | username 
----+----------
  1 | Alice
(1 row)

Common Pitfalls

Common mistakes include:

• Not enabling the extension before using citext type, causing errors.
• Using text type and expecting case-insensitive behavior without explicit functions.
• Forgetting that citext affects comparisons but stores the original case.

sql

/* Wrong: Using citext without enabling extension */
-- CREATE TABLE test (name CITEXT);

/* Right: Enable extension first */
CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE test (name CITEXT);

Quick Reference

Command	Description
CREATE EXTENSION IF NOT EXISTS citext;	Enable the citext extension in the current database
column_name CITEXT	Declare a column with case-insensitive text type
SELECT ... WHERE column = 'value';	Case-insensitive comparison on citext columns
DROP EXTENSION citext;	Remove the citext extension if no longer needed

Key Takeaways

Enable the citext extension with CREATE EXTENSION before using the citext type.

Use citext as a column type to get automatic case-insensitive text comparisons.

citext stores text with original case but compares ignoring case.

Without citext, text comparisons are case-sensitive by default in PostgreSQL.

Remember to enable the extension in each database where you want to use citext.