Postgres ships a small core and lets everything else plug in as an extension — a packaged bundle of types, functions, operators, and even index support that you switch on with a single command. This lesson installs one (citext), watches it change how equality works, and then tours the extensions worth knowing about.
The seed is a four-row users table whose emails were typed with inconsistent capitalization — the kind of mess an extension is about to clean up.
SELECT * FROM users ORDER BY id;
What's on the shelf
Every extension available to install shows up in the pg_available_extensions catalog. Have a look at what your server carries:
SELECT name, default_version, left(comment, 40) AS comment
FROM pg_available_extensions
ORDER BY name
LIMIT 12;
That's the catalog of what you could install. A separate catalog, pg_extension, lists what's actually installed in this database right now — and on a fresh database it's just plpgsql, the one that powers CREATE FUNCTION:
SELECT extname, extversion FROM pg_extension ORDER BY extname;
The email problem
Plain text compares byte-for-byte, so capitalization matters. Ada's email is stored as Ada@Example.com, and a lookup with the lowercase form finds nothing:
SELECT * FROM users WHERE email = 'ada@example.com';
Zero rows. You could paper over it with lower(email) = lower('ada@example.com') on every query, but that's easy to forget and it can't back a UNIQUE constraint. A dedicated type is cleaner.
Install an extension
CREATE EXTENSION loads a bundle into the current database. The citext extension adds a case-insensitive text type. Install it:
CREATE EXTENSION IF NOT EXISTS citext;
IF NOT EXISTS makes the command safe to run twice — a second call is a no-op instead of an error, which matters in migrations. Note extensions are installed : this lives in only, not server-wide.