CODEWITHSUNDEEP
postgresqlcollationcase-insensitive
cod3r
cod3r

Reputation:

Storing a case insensitive varchar in PostgreSQL

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 PostgreSQL, a little syntax help will be greatly appreciated.

Upvotes: 11

Views: 10779

Answers (3)

bortzmeyer
bortzmeyer

Reputation: 35529

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

Upvotes: 5

Patryk Kordylewski
Patryk Kordylewski

Reputation: 1269

From the docs

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

Upvotes: 22

ojblass
ojblass

Reputation: 21640

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.

Upvotes: 4

Related Questions