Unique model field and case sensitivity (postgres)

[chefsmart]

Reading through some discussions on this mailing list and elsewhere, I
saw a lot of material on mysql case sensitivity and collation.

Consider the following situation: -

Suppose my app allows users to create the states / provinces in their
country. Just for clarity, we are considering only ASCII characters
here.

In the US, a user could create the state called "Texas". If this app
is being used internally, let's say the user doesn't care if it is
spelled "texas" or "Texas"

But importantly, the system should prevent creation of "texas" if
"Texas" is already in the database.

If the model is like the following:

class State(models.Model):
name = models.CharField(max_length=50, unique=True)

The uniqueness would be case-sensitive in postgres, that is, postgres
would allow the user to create both "texas" and "Texas" as they are
considered unique.

What can be done in this situation to prevent such behavior. How does
one go about providing case-INsenstitive uniqueness with Django and
Postgres

[chefsmart]

By the way, right now I'm using a State.objects.get(name__iexact=name)
in clean_name() method in a form subclass to prevent creation of case-
insensitive duplicates. Just wondering if there is a built-in or
better way.

[David De La Harpe Golden]

chefsmart wrote:
> By the way, right now I'm using a State.objects.get(name__iexact=name)
> in clean_name() method in a form subclass to prevent creation of case-
> insensitive duplicates. Just wondering if there is a built-in or
> better way.
>

Well, in postgresql itself one can make a case insensitive unique
constraint using "functional index" functionality:

create unique index app_state_lower_name_idx on app_state(lower(name));

as per: http://archives.postgresql.org/pgsql-general/2005-09/msg00842.php


I guess django's ORM would need some surgery to make it automagically
generate and manage such constraints though.

[George Orw]

So if i add unique index to a column in PostgreSQL, do i need to do any other changes in the django itself ? 

I am asking this because, MySQL have this functionality enabled by default without creating any special methods.