The original question has already been answered really well, but it reminds me to mention that
Postgres text/varchar values are case-sensitive. Here's a list of the times when I would like a case-sensitive text field:
Never
Now here's the list of times I would like a case-blind text field:
Everywhere else.
If this is how you feel too, there are several alternatives. The one that I've chosen is to use the citext extension instead of text fields.This takes care of the problem without having to add extra function calls to your queries, do anything special with indexes, etc.
If you have JSON, which has case-sensitive element names, use JSONB.
Your requirements may differ than mine! Other people have good reason to want case-sensitive searches. I just never do. (30+ years in programming and I can't remember a time I wanted user data to be treated case-sensitively...but you never know...one day...maybe.) There's also an extension for stripping accents, which I've not needed.
I've idly wondered if using a different collation on a text field might be a better answer than using citext everywhere? If anyone wants to set me straight on this, I'd be grateful.
