Case insensitive in Postgres

Question

I am currently doing a Java project which has been in development for a few months. The project is almost complete, and now I realized that Postgres is a case sensitive.

During my login process, it can't differentiate between username and "UserName". I am using Java base, Hibernate and JPA as the backend implementation, and using Postgres as the database.

I have been searching internet for the solutions, and most of the answer I get were suggesting me using solution:

select loginId from user where 
lower(loginId)=loginid.toLowerCase();

or change the data type for the table column to citext.

But is there any faster way of doing this? Or maybe is there any variable in Postgres.conf able to control case insensitive? Or in hibernate I able to control it easily?

I understand that using method lower(loginId)=loginId.toLowerCase() is the easiest way to fix the problem, but there is a lot of code that I need to change in my project, which might take a long time to fix. Change the data type to citext is not really a good solution for a project which has implemented long ago.

Is anyone here has a better solution to fix my current issue?

Answer 1

PostgreSQL has no case-insentitive collations, either globally or on a per-column/operator basis. Nor does it have a "case sensitivity off" configuration option.

Your options are:

• Use the citext type on cols you want case-insitive. Probably the best option if the column should always be case insensitive; this is what I would do, unless Hibernate had issues with it. Importantly, citext is case-insensitive case-preserving, i.e. capitals are retained, they just aren't considered significant in comparisions.

• Explicitly compare lower-cased values, per lower(col) = lower('Literal'). Cannot use an index on col, you must create a separate index on lower(col). A unique constraint (including primary key) on col won't be case-insensitive; you have to create a unique index on lower(col) instead if you want this. This approach is case-preserving.

• Add a CHECK constraint on col that permits only lower-case values and rejects upper-case ones. You can combine this with a BEFORE INSERT OR UPDATE ... FOR EACH ROW trigger that lower-cases inputs if you want. That way the values of the column can only be lower case. Then you just have to make sure the client application always lower-cases values for comparisions. This approach is not case-preserving - when you input BOB, the database stores bob and there's no way to later know which was input.

• Since you're using an ORM, you can do the lower-casing in the model code instead. Just have your entity always toLowerCase inputs and outputs on the accessors. You still have to remember to lowercase the other side values of comparisons in your code, but it's a lot less fragile. Again, this is not case-preserving.

Personally, I'd just use citext where appropriate, that's what it's for.

A global switch for case sensitivity in PostgreSQL could only be possible at initdb time - otherwise turning case sensitivity on or off would suddenly make previously valid unique constraints violated, previously valid foreign key constraints violated, break check constraints, etc. It'd also violate the SQL standard, which requires case-sensitive operation. So PostgreSQL doesn't support it.

What would be useful is if the per-column and per-operator collation overrides supported case insensitive collations. However, they don't.

Answer 2

You can try this out and see if it works for you. I recommend to use this as a temp, until you fix your program.

SELECT loginID 
FROM user 
WHERE (SELECT CHAR_LENGTH(REGEXP_REPLACE(loginId,*loginId from java*,'','ig')) = 0)

This should provide you the loginID if the loginID matches without checking for case sensitivity. Also if you have more than one of the same login but different cases, then this WILL NOT work for you.

Here is a example of the output using one of my test tables in my database. username is all uppercase in my table.

select username from test.person where (select char_length(regexp_replace(username,'jMeRlOs','', 'ig')) = 0)

Result:

Answer 3

You have the right idea, you just need an expression-based index on lower(loginId). See

http://www.postgresql.org/docs/9.1/static/indexes-expressional.html

for details on expression based indexes. In your case, you will want to create:

CREATE INDEX tablename_lower_loginId_idx ON tablename (lower(loginId));

If you already have code that doesn't do this, you could perhaps rename your table and create a view instead. Then your code will read off of your view, but you will still have to change the name of the table you write to.

One more note: for loginId's, you will be better off only storing lowercase values in the first place as mentioned in another answer. Perhaps you could create a new column, and copy all of the lowercased loginId's into the new column and then set a constraint to make sure no new invalid entries get in. Then replace the old column with the new column and make sure you only pass lower-cased values to your back-end.