How do I create an index in PostgreSQL based on lowercase only?

You can create the index and transform the field to upper- or lower-case. Then when you do your queries, you can do the same transform and it’ll do the right thing.

So:

CREATE UNIQUE INDEX lower_case_username ON users ((lower(username)));

Then query for the same thing:

SELECT username FROM users WHERE lower(username) = 'bob';