How to enforce lowercase for all data in specific column?
You need a trigger for that:

create function make_lower_name() 
  returns trigger
as
$$
begin
  new.name := lower(new.name);
  return new;
end;
$$
language plpgsql;

create trigger ensure_lower_name_trg
   before update or insert on users
   for each row
   execute procedure make_lower_name();