Postgres database with custom collation

I created a Postgresql database and started using it. I want to search for strings in case Insensitive but it does not work because the database collation is “en-US.UTF8” that is sensitive.

How can I have the all the tables case Insensitive without having to use “lower” function in all my queries?
Is there any possibility of render changing the collation of my database?

Hi there,

What collation would you like to use?

From my understanding, you would need to set it on database creation to have it work database-wide. You can’t change it once the database is created. You can also do it on a per table/field level, but you would need to alter your tables.

Also note, that if you do care in some cases about case sensitivity, you would need to it at a table/field level.


Render Support, UTC+10 :australia:

1 Like

Hi Keith,
thanks for your answer .

I would like to use some case insensitive collation that works but for what I read in Postgresql there is no built in collation for that purpose so I should need to use CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false); and create the database or all the tables with this collation.

And I need it in every table so it is not viable for me to alter all my tables or table/field.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.