postgresql Django 中不区分大小写的唯一模型字段？

Case insensitive unique model fields in Django?

提问by noobzie

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:

我有以下要求：

• field is CharField compatible
• field is unique, but case insensitive
• field needs to be searchable ignoring case (avoid using iexact, easily forgotten)
• field is stored with case intact
• preferably enforced on database level
• preferably avoid storing an extra field

• 字段与 CharField 兼容
• 字段是唯一的，但不区分大小写
• 字段需要可搜索，忽略大小写（避免使用 iexact，容易忘记）
• 字段保存完好
• 最好在数据库级别强制执行
• 最好避免存储额外的字段

Is this possible in Django?

这在 Django 中可能吗？

The only solution I came up with is "somehow" override the Model manager, use an extra field, or always use 'iexact' in searches.

我想出的唯一解决方案是“以某种方式”覆盖模型管理器，使用额外的字段，或者在搜索中始终使用“iexact”。

I'm on Django 1.3 and PostgreSQL 8.4.2.

我使用的是 Django 1.3 和 PostgreSQL 8.4.2。

采纳答案by Erwin Brandstetter

Store the original mixed-case string in a plain text column. Use the data type textor varcharwithout 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 manualor in this related answer by Peter Eisentraut @serverfault.SE.

将原始大小写混合字符串存储在纯文本列中。使用数据类型text或varchar不使用长度修饰符而不是varchar(n). 它们本质上是相同的，但是使用 varchar(n) 您必须设置任意长度限制，如果您以后想更改，这可能会很痛苦。在手册或Peter Eisentraut @serverfault.SE 的相关答案中阅读更多相关信息。

Create a functional unique indexon lower(string). That's the major point here:

创建一个功能独特的指数上lower(string)。这是这里的主要观点：

CREATE UNIQUE INDEX my_idx ON mytbl(lower(name));

If you try to INSERTa 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:

如果您尝试INSERT使用已经存在的小写混合大小写名称，则会出现唯一键违规错误。
对于快速相等搜索，请使用如下查询：

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.

顺便说一句：您可能想要升级到更新版本的 PostgreSQL。自 8.4.2 以来，有许多重要的修复。更多关于官方 Postgres 版本控制站点的信息。

回答by Rodney Folz

As of Django 1.11, you can use CITextField, a Postgres-specific Field for case-insensitive text backed by the citext type.

从 Django 1.11 开始，您可以使用CITextField，这是一个 Postgres 特定的字段，用于由 citext 类型支持的不区分大小写的文本。

from django.db import models
from django.contrib.postgres.fields import CITextField

class Something(models.Model):
    foo = CITextField()

Django also provides CIEmailFieldand CICharField, which are case-insensitive versions of EmailFieldand CharField.

在Django中还提供了CIEmailField和CICharField，这是不区分大小写的版本EmailField和CharField。

回答by Chris Pratt

With overriding the model manager, you have two options. First is to just create a new lookup method:

通过覆盖模型管理器，您有两个选择。首先是创建一个新的查找方法：

class MyModelManager(models.Manager):
   def get_by_username(self, username):
       return self.get(username__iexact=username)

class MyModel(models.Model):
   ...
   objects = MyModelManager()

Then, you use get_by_username('blah')instead of get(username='blah'), and you don't have to worry about forgetting iexact. Of course that then requires that you remember to use get_by_username.

然后，您使用get_by_username('blah')代替get(username='blah')，您不必担心忘记iexact。当然，这要求您记得使用get_by_username.

The second option is much hackier and convoluted. I'm hesitant to even suggest it, but for completeness sake, I will: override filterand getsuch that if you forget iexactwhen querying by username, it will add it for you.

第二种选择更加hackier和复杂。我什至不愿意建议它，但为了完整起见，我会：覆盖filter，get这样如果您iexact在按用户名查询时忘记了，它会为您添加它。

class MyModelManager(models.Manager):
    def filter(self, **kwargs):
        if 'username' in kwargs:
            kwargs['username__iexact'] = kwargs['username']
            del kwargs['username']
        return super(MyModelManager, self).filter(**kwargs)

    def get(self, **kwargs):
        if 'username' in kwargs:
            kwargs['username__iexact'] = kwargs['username']
            del kwargs['username']
        return super(MyModelManager, self).get(**kwargs)

class MyModel(models.Model):
   ...
   objects = MyModelManager()

回答by Zorg

You can use citext postgres type instead and not bother anymore with any sort of iexact. Just make a note in model that underlying field is case insensitive. Much easier solution.

您可以改用 citext postgres 类型，而不再使用任何类型的 iexact。只需在模型中记下基础字段不区分大小写即可。更容易的解决方案。

回答by Suraj

Since a username is always lowercase, it's recommended to use a custom lowercase model field in Django. For the ease of access and code-tidiness, create a new file fields.pyin your app folder.

由于用户名总是小写，建议在 Django 中使用自定义的小写模型字段。为了便于访问和代码整洁，请fields.py在您的应用程序文件夹中创建一个新文件。

from django.db import models
from django.utils.six import with_metaclass

# Custom lowecase CharField

class LowerCharField(with_metaclass(models.SubfieldBase, models.CharField)):
    def __init__(self, *args, **kwargs):
        self.is_lowercase = kwargs.pop('lowercase', False)
        super(LowerCharField, self).__init__(*args, **kwargs)

    def get_prep_value(self, value):
        value = super(LowerCharField, self).get_prep_value(value)
        if self.is_lowercase:
            return value.lower()
        return value

Usagein models.py

使用中models.py

from django.db import models
from your_app_name.fields import LowerCharField

class TheUser(models.Model):
    username = LowerCharField(max_length=128, lowercase=True, null=False, unique=True)

End Note: You can use this method to store lowercase values in the database, and not worry about __iexact.

尾注：您可以使用此方法将小写值存储在数据库中，而不必担心__iexact.

回答by Luan Francisco Lima Fernandes

You can use lookup='iexact' in UniqueValidator on serializer, like this: Unique model field in Django and case sensitivity (postgres)

您可以在序列化程序的 UniqueValidator 中使用 lookup='iexact'，如下所示：Unique model field in Django andcasesensitive (postgres)

回答by NKSM

You can also override "get_prep_value" by Django Models Field

您还可以通过 Django 模型字段覆盖“get_prep_value”

class LowerCaseField:
    def get_prep_value(self, value):
        if isinstance(value, Promise):
            value = value._proxy____cast()
        if value:
            value = value.strip().lower()
        return value


class LCSlugField(LowerCaseField, models.SlugField):
    pass


class LCEmailField(LowerCaseField, models.EmailField):
    pass

email = LCEmailField(max_length=255, unique=True)