The PostgreSQL citext type (docs here) is a good choice for lots of user-facing text.
It stores the text with the case given, but for comparisons it ignores case.
You probably want searches to be case insensitive; eg, searching for "alex" should match "Alex". citext means you don't have to remember to convert the search term and column value to lowercase when searching.
Also, if you want to sort names alphabetically, you probably want Banana to come after apple, even though upper case Latin characters have smaller codepoint values than lower case ones (see the decimal set in man ascii). Since sorting involves comparison, citext takes care of this.
And if you have a unique index, you probably don't want to allow both foo@example.com and Foo@example.com. Again, since unique indexes involve comparison, citext means these values conflict.
Note that citext is an extension; you have to CREATE EXTENSION IF NOT EXISTS citext in order to use it.
Here are a few quick demos.
Ordering
WITH test_data(name) AS (
VALUES
('Banana'),
('APE'),
('apple')
)
SELECT name FROM test_data ORDER BY name ASC;
name
--------
APE
Banana
apple
(3 rows)vs
WITH test_data(name) AS (
VALUES
('Banana'::citext),
('APE'::citext),
('apple'::citext)
)
SELECT name FROM test_data ORDER BY name ASC;
name
--------
APE
apple
Banana
(3 rows)Uniqueness
WITH test_data(name) AS (
VALUES
('Apple'),
('apple'),
('APPLE')
)
SELECT DISTINCT name FROM test_data;
name
-------
APPLE
apple
Apple
(3 rows)vs
WITH test_data(name) AS (
VALUES
('Apple'::citext),
('apple'::citext),
('APPLE'::citext)
)
SELECT DISTINCT name FROM test_data;
name
-------
Apple
(1 row)