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 afterapple
, even though upper case Latin characters have smaller codepoint values than lower case ones (see the decimal set inman 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
andFoo@example.com
. Again, since unique indexes involve comparison,citext
means these values conflict.Note that
citext
is an extension; you have toCREATE EXTENSION IF NOT EXISTS citext
in order to use it.Here are a few quick demos.
continue reading on nathanmlong.com
⚠️ This post links to an external website. ⚠️
If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.