create unique index some_name on users (lower(username));

create unique index some_name on users (lower(username)) where is_active = true;

select * from users where lower(username) = '...'

select * from users where lower(username) like '...'

select * from users where username ilike '...'

CREATE UNIQUE INDEX
    example_unique_idx
ON
    example_table ((LOWER(case_insensitive_field)));