Using "citext" in PostgreSQL for case insensitive comparisons
I think its new in PostgreSQL 8.4, there is a new field type called citext, which stands for case insensitive text. Currently on Ubuntu 9.10 its in the PostgreSQL contrib package, which often serves as a testbed for features before they are adopted into PostgreSQL proper.
Using citext ensures that all comparisons are case insensitive, which includes referential integrity on inserts, updates and deletes, and SQL queries for lookups for example, which I think will work well for those fields that should always be compared case insensitive.
Although citext (and probably many other modules) are not listed on the contrib page, they are included, you can see them in the complete file list.
Steps to install on Ubuntu 9.10 (Karmic Koala):
# Install the contrib package
sudo apt-get install postgresql-contrib-8.4
# Add the citext module to a database of your choice
psql [username] -d [databaseName] -f /usr/share/postgresql/8.4/contrib/citext.sql
Where in this case /usr/share/postgresql/8.4 is the PostgreSQL SHARE_DIR folder.
Now you can use the field citext instead of text. If you'd like to use citext on Ubuntu 9.04 Jaunty (which only includes PostgreSQL 8.3), then check out Mark's post about installing PostgreSQL 8.4 on Ubuntu 9.04.
Comments [0]