citext, Collations & Accent-Insensitive Search

Make text equality work for humans — case and accent insensitive by default

A user signs up with alice@example.com. Later they try to log in with Alice@Example.com. In a plain text column, these are two different values. The login fails.\n\nThe social_db users table uses citext for the email column — a PostgreSQL type that performs all comparisons in a case-insensitive way. No application logic needed. No LOWER() wrapping. The column behaves the way humans expect.\n\nCzech names like "Ãlice" and "alice" are a further challenge: diacritics matter in some contexts and not in others. The unaccent extension strips diacritics for search while preserving them in storage.

The Problem with Case-Sensitive Text

-- Plain text column:
SELECT * FROM users WHERE email = 'alice@example.com';   -- ✅ finds row
SELECT * FROM users WHERE email = 'Alice@example.com';   -- ❌ returns nothing
SELECT * FROM users WHERE email = 'ALICE@EXAMPLE.COM';   -- ❌ returns nothing

For emails and usernames, case should not matter. Three approaches:

Option 1: citext Column Type (Best for email/username)

-- social_db.users.email is citext
SELECT id, username, email FROM users WHERE email = 'ALICE@EXAMPLE.COM';
-- ✅ Finds the row — citext compares case-insensitively, always

citext is a PostgreSQL extension that stores the original value but compares as if both sides were lowercased. UNIQUE constraints on citext columns are also case-insensitive.

Option 2: LOWER() Wrapper on plain text

-- Works but bypasses indexes unless you create a functional index on LOWER(col)
SELECT * FROM users WHERE LOWER(username) = LOWER('Alice_Wonder');

Option 3: unaccent() for Accent-Insensitive Search

-- Requires: CREATE EXTENSION IF NOT EXISTS unaccent;
-- Strip diacritics from both the column and the search term
SELECT * FROM users
WHERE unaccent(LOWER(display_name)) = unaccent(LOWER('alice smith'));
-- Finds "Alice Smith", "Ãlice Smith", "ALICE SMITH"

What Is a Collation?

A collation defines the rules for comparing and sorting text:

Are 'a' and 'A' equal?
Does 'ä' sort after 'z' or next to 'a'?
Does 'ch' sort as one character or two (Spanish)?

PostgreSQL databases are created with a collation (shown in \l). Individual columns or ORDER BY clauses can override it:

SELECT display_name
FROM users
ORDER BY display_name COLLATE "cs-CZ-x-icu";  -- Czech alphabetical order

citext

A PostgreSQL extension type that behaves identically to text for storage, but performs all comparisons (=, <, >, LIKE, UNIQUE) case-insensitively. The stored value is preserved exactly as entered — only comparisons are case-folded. It requires the citext extension to be installed in the database.

unaccent()

A PostgreSQL function (from the unaccent extension) that strips diacritical marks from text: ä→a, é→e, ñ→n, ü→u. Used to enable accent-insensitive search without losing the original accented value in storage. Typically combined with LOWER() for full case- and accent-insensitive matching.

🔌 Connect to social_db

Connect to social_db as the social_db user. This database has the users table with a citext email column.

psql -U social_db -d social_db

psql (18.4) Type "help" for help. social_db=>

🔍 Inspect the users Table

Run \d users and find the email column. Notice its type is citext, not text. The UNIQUE constraint on citext is also case-insensitive.

\d users

✉️ Query by Email in Any Case

Find the user with email user1@example.com using three different casings: lowercase, uppercase, and mixed case. All three should return the same row.

SELECT username, email, display_name FROM users WHERE email = 'user1@example.com';

SELECT username, email, display_name FROM users WHERE email = 'USER1@EXAMPLE.COM';

SELECT username, email, display_name FROM users WHERE email = 'User1@Example.COM';

username | email | display_name ----------+-------------------+-------------- user_0001 | user1@example.com | Alice Smith (1 row)

🔡 LOWER() Workaround on a plain text column

The username column is plain text. Find users whose username matches "user_0002" regardless of case, using LOWER() on both sides of the comparison.

SELECT username, display_name FROM users WHERE LOWER(username) = LOWER('USER_0002');

username | display_name ----------+-------------- user_0002 | Bob Jones

🌐 Accent-Insensitive Search with unaccent()

Use unaccent() to find users by display name regardless of diacritics. Search for users named "alice smith" and confirm it finds "Alice Smith" even without matching the exact accenting.

SELECT username, display_name FROM users WHERE unaccent(LOWER(display_name)) = unaccent(LOWER('Julie de la Leveque'));

username | display_name ----------+-------------- user_0001 | Alice Smith

Lab complete! You can handle case- and accent-insensitive text correctly:\n\n\n citext column : ✅ all comparisons case-insensitive by default\n LOWER(col) : ✅ workaround on plain text (watch the index!)\n unaccent() : ✅ strip diacritics for accent-insensitive search\n COLLATE "..." : ✅ override sort order per column or expression\n

Enable JavaScript to run the live terminal and track your progress.