How can I update my Postgresql database to be case insensitive ?

I already have like some tables and some data, and currently they are case sensitive but I would like to update all of them to be case insensitive.

CodePudding user response：

There's a question about this related to emails that has useful insight: PostgreSQL: Case insensitive string comparison

Basically it seems there are a few approaches. You Can

1. Use lower() in indexes and make sure to use it on every query clause including this column.
2. Convert to citext (available in postgres 9) which internally compares by using lower() while retaining normal behavior of text otherwise.
3. Worst of all, probably, you could just force data to lower case on insert with a database trigger.

CodePudding user response：

You cannot get your database to be case insensitive, but from v12 on you can create a case insensitive ICU collation and use that with column definitions:

CREATE COLLATION english_ci (
   PROVIDER = 'icu',
   LOCALE = 'en-US@colStrength=secondary',
   DETERMINISTIC = FALSE
);

That could be used like this:

CREATE TABLE testtab (ci_col text COLLATE english_ci);

Comparisons are case insensitive:

SELECT 'Hello' = 'hello' COLLATE english_ci;

 ?column? 
══════════
 t
(1 row)