Postgresql With Case-Insensitive Text
I got put off using CITEXT by the tip at the top of the documentation suggesting to use a non-deterministic collation instead. So I did for a small project.
After a bit of faffing about, I came up with using two collations, one insensitive and one sensitive:
CREATE COLLATION en_us_ci (PROVIDER = icu, LOCALE = 'en-US-u-ks-level2', DETERMINISTIC = FALSE);
CREATE COLLATION en_us_cs (PROVIDER = icu, LOCALE = 'en-US-u-ks-level2', DETERMINISTIC = TRUE);
Then created a domain to make it easy to use the case insensitive version:
CREATE DOMAIN itext AS text COLLATE en_us_ci;
So basically, I then used itext whereever I would normally use varchar or text, and wanted a case insensitive version.
The gotcha was that LIKE does not work with non-deterministic collations. This is where the en_us_cs collation came in handy.
Trying to run a query with a WHERE clause like:
first_name LIKE '%george%'
Would cause an error. Instead I would write:
first_name COLLATE "en_us_cs" ILIKE '%george%'
The other gotcha would be that the above collations may be operating system specific (Linux in this case), and you may have trouble copying the database to another OS.
Is this better than using CITEXT? Did I introduce any foot-guns? I honestly have no idea. The project I used it for looks to work well and is performant for my needs. Would love to receive feedback.
Sitewide Feed
