🔗 Try PostgreSQL's citext

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.