Case-insensitive text, citext:
create extension citext;
create table if not exists users (
email_ci citext unique,
email text unique
);
insert into users (email_ci, email)
values ('[email protected]', '[email protected]');
select * from users where email_ci = '[email protected]';