caSe-inSENsiTive

Postgres FM

Nikolay and Michael discuss case-insensitive data — when we want to treat columns as case-insensitive, and the pros and cons of using citext, functions like lower(), or a custom collation.
Here are some links to things they mentioned:

• citext https://www.postgresql.org/docs/current/citext.html
• Our episode on over-indexing https://postgres.fm/episodes/over-indexing
• Nondeterministic collations https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC
• How to migrate from Django’s PostgreSQL CI Fields to use a case-insensitive collation (blog post by Adam Johnson) https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-case-insensitive-collation
• The collation versioning problem with ICU 73 (blog post by Daniel Vérité) https://postgresql.verite.pro/blog/2023/10/20/icu-73-versioning.html
• amcheck https://www.postgresql.org/docs/current/amcheck.html

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With credit to:

• Jessie Draws for the elephant artwork