Case insensitive database lookup with index?

Question

I am using Postgres.

I have an Artices table in my database with a column url for urls. This means that I can display the articles in this table on the website, not " example.com/23323", but " example.com/Funny_Thing_Happened_to_Me" instead . It was simple enough to implement, and then, when the number of articles grew, I added an index to the table on URL strings.

Since then, I realized that although I want to be able to display capital letters in URLs, I want them to be case insensitive in terms of what the user enters, and I want to ensure that URLs are unique in case of insensitive way.

Is there an easy way to quickly search based on a text column case-insensitive, and also to ensure uniqueness in the case-insensitive case?

I tried to search with something like lower(url) =, but this makes Postgres decide not to use the index at all.

Answer 1

Use the functional index :

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

If you are on 8.4 and can install the contrib module, also take a look at the citext type . It abstracts all the lower / upper parts and performs a little better.

Answer 2

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

, ? " "?

, "lower()":

SELECT * FROM sometable WHERE UPPER(textfield) LIKE (UPPER('value') || '%');