11

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.

Improve this question
4
  • You can make queries that are case insensitive, and even make indexes to support those queries - is that what you mean? Commented Jan 17, 2022 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 Commented Jan 17, 2022 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 Commented Jan 17, 2022 at 10:25
  • See here or here or here Commented Jan 17, 2022 at 10:29

1 Answer 1

Reset to default
8

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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ā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.
Oh, so I have to create an index with the same collation as well. All right, thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.