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.
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.
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') || '%');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With