Re: postgresql database use a case insensitive collation

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