Thread: postgresql database use a case insensitive collation

On Wed, 2022-05-25 at 09:19 +0300, Dennis wrote:
> How to make configuration my postgresql database use a case insensitive collation?

You'd have to define such a collation with CREATE COLLATION and you have to use it
in column definitions explicitly, since such a collation cannot be used as database
collation.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Am 25.05.22 um 08:19 schrieb Dennis:
> Hi,
>
> How to make configuration my postgresql database use a case 
> insensitive collation?
>
> Dennis

Hi Dennis,

you may use the data type citext (case insensitive text). It's not 
builtin, but an extension, which has to be activated in every database with

CREATE EXTENSION citext;

Regards,

Holger


-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

On 25/05/2022 07:19, Dennis wrote:
> How to make configuration my postgresql database use a case 
> insensitive collation?
>
Someone will probably tell me that it's not perfect, but I just use 
UPPER() when searching, and create indexes using UPPER() on the fields 
where I want case insensitivity.

eg:

create table books (title varchar, author varchar);

create index books_title on books (upper(title));

then

select * from books where upper(title) = upper('Return Of The Fellowship');

or

select * from books where author = 'J R R Tolkien' order by upper(title);

(These both use the 'books_title' index)


 From experience, it's a lot easier to use case-insensitive search/order 
when the database is case sensitive than it is the other way around!


Paul


-- 


Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53

Sign up for news & updates at http://www.pscs.co.uk/go/subscribe