Change postgres to case insensitive

Question

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.

Answer 1

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)