How to make postgresql queries case insensitive

Scenarios when we want postgresql to act as case insensitive. We can convert columns to uppercase or lowercase using upper()/lower() to make comparisons case insensitive.
Scenarios where we would need to make such changes -

• where conditions
• Join conditions
• group by — here if put lower/upper on column then that column in select statement should have either lower/upper or other aggregate like max.
• order by columns
• having conditions
• ilike to make like case insensitive
• instead of select distinct lower(col1) from… use select distinct on (lower(col1)) col1 from… as it won’t convert value to lower case while being case insensitive.
• unique constraints are case sensitive. if using on conflict clause in insert queries then it would be case sensitive. the only solution is to remove the conflict and add extra where condition of “AND NOT EXISTS ( select 1 from … where unique constriants columns wrapped by lower())”. If on conflict () update then add update query before the insert query with extra conditions of lower() wrapped the unique constraint columns and “NOT EXISTS …” in the afterward insert query.