Storing a case insensitive VarChar in Postgres SQL

Question

I want to add some constraint to my username VARCHAR in the sql table so that if a username exists, a duplicate username in a different case cannot be created. How can I do this? thanks

edit: I am using postgres sql, a little syntax help will be greatly appreicated

Answer 1

If the tables are not yet populated you may consider simply converting to a standard upper or lower case prior to doing any insertions and making the field a primary key (or just have a unique constraint). If the user want to see his userid in the case he specified this could be another column in the database.

Update:Based on the updated tags I would still suggest the solution I have proposed as being less dependent on a particular DBMS.

Answer 2

From the docs

CREATE UNIQUE INDEX lower_title_idx ON films ((lower(title)));

Answer 3

Do note that PostgreSQL 8.4 (currently beta) will have a case-insensitive text type.