How to make PostgreSQL case insensitive?

Question

How to make PostgreSQL search (WHERE field = 'stackoverflow') case insensitive?

I don't want to use LOWER() because of ORM and CITEXT because of less efficiency. I found the solution is to use another collation:

CREATE DATABASE polish_database  LC_COLLATE 'pl_PL.UTF-8' LC_CTYPE
'pl_PL.UTF-8';

But how to download case insensitive collation/locale to Ubuntu?

Answer 1

Not sure if this help.

But you dont need use the collate for the whole database.

you can asign the collate just to the field you are worry about it.

CREATE TABLE test1c (
    id integer,
    content varchar COLLATE "x"
);

Also you can create an index to support collate or with a calculate value

CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");

OR

CREATE INDEX test1c_content_y_index ON test1c (LOWER(content));

Then you can do lower() and use index.

WHERE field = Lower('StackoverFlow')