7

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.

  • You can make queries that are case insensitive, and even make indexes to support those queries - is that what you mean? – Jiří Baum Jan 17 '22 at 10:03
  • So far I've only used MSSQL (which by default is case insensitive - I've never touched this part so this is just my guess), now I'm on a new project which uses postgresql and it seems that they are facing issues with string searches (this makes me believe that the engine is configured as case sensitive by default). What I want to do is to update this setting on the engine level so I don't have to go and update any of my code –  Jan 17 '22 at 10:09
  • Do you mean that when you write your query you have to enclose the column name with double quotes ? ex: `select "Column1","Column2" from "Table"`. If this is it, you have make `select * into table1 from "Table"` then `drop table "Table"` and finally `rename table1 as table` – Philippe Jan 17 '22 at 10:25
  • See [here](https://stackoverflow.com/a/59101567/) or [here](https://dba.stackexchange.com/questions/255780/case-insensitive-collation-still-comparing-case-sensitive) or [here](https://dba.stackexchange.com/questions/191905/does-postgresql-support-icu-collations-options-and-settings) –  Jan 17 '22 at 10:29

2 Answers2

5

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)
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Does this have any bad effect on the performance? Can indexes be still used if I specify the collation in a select query for a column? – Jānis Elmeris May 30 '23 at 11:40
  • 3
    @JānisElmeris Yes, you can use an index if it was created using the same collation as in the query. And yes, case insensitive search is always slower, that is in the nature of things. With an index it shouldn't matter so much. – Laurenz Albe May 30 '23 at 12:17
  • Oh, so I have to create an index with the same collation as well. All right, thanks! – Jānis Elmeris May 30 '23 at 12:40
1

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.
E_net4
  • 27,810
  • 13
  • 101
  • 139
samiam2013
  • 13
  • 3