Postgres case sensitivity : Implications of Lower()

I have run into the issue of case sensitive searching in postgres, and have started to deal with this by using LOWER on each side of every WHERE test.

So far so good. However, I understand that in order to make use of indexes, they should be created use LOWER too, which makes sense.

However, what of the PK? presumably these are not going to be effective because it does not seem possible to create a PK using a function on the chosen PK field. Isnt this a concern for any filtering or joining which is done on PKs?

Is there a way of working around this ?

Solution

You can still create a functional index on PK (even consisting of many columns):

CREATE TABLE test(a text, b text, c text, d text, primary key (a,b,c));
CREATE INDEX ON test (lower(a), lower(b), lower(c));

Though, it sounds like there is need for some data improvement operations to be done if you are experiencing this kind of behaviour almost everywhere in your database (like store everything in lower case).