How would I set up an index based on lower case only?
Even though the actual field contains both upper and lower case letters.
Also, can I run a query and have only the lower case index value returned?
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';