4 ms·
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 t
by rarrrrrr 9y 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');
- olavgg 9y agoactually it is easier SELECT 1 FROM user_email WHERE email ILIKE 'Foo@Foo.coM';
- always_good 9y agoWHERE 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.
- olavgg 9y agoIt can if you use the pg_trgm extension, a good summary can be read here: https://niallburkley.com/blog/index-columns-for-like-in-postgres/ https://niallburkley.com/blog/index-columns-for-like-in-post...