I have basically a username is unique (case insensitive), but the case matters when displaying as provided by the user.
I have the following requirements:
Is this possible in Django?
The only solution I came up with is "somehow" override the Model manager, use an extra field, or always use 'iexact' in searches.
I'm on Django 1.3 and PostgreSQL 8.4.2.
Store the original mixed-case string in a plain text column. Use the data type text
or varchar
without length modifier rather than varchar(n)
. They are essentially the same, but with varchar(n) you have to set an arbitrary length limit, that can be a pain if you want to change later. Read more about that in the manual or in this related answer by Peter Eisentraut @serverfault.SE.
Create a functional unique index on lower(string)
. That's the major point here:
CREATE UNIQUE INDEX my_idx ON mytbl(lower(name));
If you try to INSERT
a mixed case name that's already there in lower case you get a unique key violation error.
For fast equality searches use a query like this:
SELECT * FROM mytbl WHERE lower(name) = 'foo' --'foo' is lower case, of course.
Use the same expression you have in the index (so the query planner recognizes the compatibility) and this will be very fast.
As an aside: you may want to upgrade to a more recent version of PostgreSQL. There have been lots of important fixes since 8.4.2. More on the official Postgres versioning site.