Hacker News new | ask | show | jobs
by rarrrrrr 3317 days ago
Here's an example of doing that in PostgreSQL:

  create table user_email (
      email text not null 
  );
  -- create a index on the lowercase form 
  -- of the email 
  create unique index user_email_case_idx 
      on user_email (lower(email));
  -- select using the index, with the lowercase form.
  select 1 
    from user_email 
   where lower(email)=lower('Foo@foo.com');
1 comments
olavgg 3316 days ago
actually it is easier

SELECT 1 FROM user_email WHERE email ILIKE 'Foo@Foo.coM';

link
always_good 3316 days ago 

    WHERE lower(email) = lower('foo@example.com')
Is simple and hits an index on lower(email).

I'm not sure ILIKE can hit an index in your example.

link
olavgg 3316 days ago
It can if you use the pg_trgm extension, a good summary can be read here: https://niallburkley.com/blog/index-columns-for-like-in-post...
link