Case insensitive search in database with an index?

Question

I'm using Postgres.

I have a table of Artices in my database, with a column url for url slugs. These are so that I can display the articles in that table on a website as not "example.com/23323" but instead as "example.com/Funny_Thing_Happened_to_Me". This was straightforward enough to implement, and then as the number of articles grew, I added an index to the table on the url slugs.

I have since realized that while I want to be able to display capitalized letters in the urls, I want them to be case insensitive in terms of what the user types in, and I want to enforce uniqueness on the urls in a case insensitive manner.

Is there a straightforward way to quickly search based on a text column in a case insensitive way, and also enforce uniqueness in a case insensitive way?

I've tried conducting the searches with something like lower(url) = but that causes Postgres to decide not to use the index at all.

Answer 1

Many databases support case-insensitivity on database or table or column level.

If that is not an option, why do you not use two columns: one in original case, and one in upper or lower case, and use the second for uniqueness checks and searching (and index it)?

Answer 2

Hi SELECT * FROM sometable WHERE textfield ILIKE 'value%';

Is this what you are looking for? What do you mean by "enforce uniqueness in a case insensitive way"?

Or this if you want to stick to the "lower()": SELECT * FROM sometable WHERE UPPER(textfield) LIKE (UPPER('value') || '%');

Answer 3

Use a functional index :

CREATE UNIQUE INDEX ix_test1 on articles (lower(url));

If you're on 8.4 and can install a contrib module, then also take a look at the citext type. It abstracts away all the lower/UPPER stuff and is slightly better performing.