postgresql Django 中的独特模型字段和区分大小写（postgres）

Question

提问by chefsmart

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.

假设我的应用程序允许用户在他们的国家创建州/省。为了清楚起见，我们在这里只考虑 ASCII 字符。

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" or "teXas"

在美国，用户可以创建名为“Texas”的州。如果此应用程序在内部使用，假设用户不关心它的拼写是“texas”还是“Texas”或“teXas”

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

但重要的是，如果“Texas”已经在数据库中，系统应该阻止创建“texas”。

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.

唯一性在 postgres 中区分大小写；也就是说，postgres 将允许用户创建“texas”和“Texas”，因为它们被认为是独一无二的。

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

在这种情况下可以做些什么来防止这种行为。如何使用 Django 和 Postgres提供不区分大小写的唯一性

Right now I'm doing the following to prevent creation of case- insensitive duplicates.

现在我正在执行以下操作以防止创建不区分大小写的重复项。

class CreateStateForm(forms.ModelForm):
    def clean_name(self):
        name = self.cleaned_data['name']
        try:
            State.objects.get(name__iexact=name)
        except ObjectDoesNotExist:
            return name
        raise forms.ValidationError('State already exists.')

    class Meta:
        model = State

There are a number of cases where I will have to do this check and I'm not keen on having to write similar iexact checks everywhere.

在许多情况下，我将不得不执行此检查，而且我并不热衷于必须在任何地方编写类似的 iexact 检查。

Just wondering if there is a built-in or better way? Perhaps db_type would help? Maybe some other solution exists?

只是想知道是否有内置或更好的方法？也许 db_type 会有所帮助？也许存在其他解决方案？

Answer 1

回答by Mayuresh

You could define a custom model field derived from models.CharField. This field could check for duplicate values, ignoring the case.

您可以定义从models.CharField. 此字段可以检查重复值，忽略大小写。

Custom fields documentation is here http://docs.djangoproject.com/en/dev/howto/custom-model-fields/

自定义字段文档在这里http://docs.djangoproject.com/en/dev/howto/custom-model-fields/

Look at http://code.djangoproject.com/browser/django/trunk/django/db/models/fields/files.pyfor an example of how to create a custom field by subclassing an existing field.

查看http://code.djangoproject.com/browser/django/trunk/django/db/models/fields/files.py以了解如何通过对现有字段进行子类化来创建自定义字段的示例。

You could use the citext module of PostgreSQL https://www.postgresql.org/docs/current/static/citext.html

您可以使用 PostgreSQL 的 citext 模块https://www.postgresql.org/docs/current/static/citext.html

If you use this module, the the custom field could define "db_type" as CITEXT for PostgreSQL databases.

如果您使用此模块，则自定义字段可以将“db_type”定义为 PostgreSQL 数据库的 CITEXT。

This would lead to case insensitive comparison for unique values in the custom field.

这将导致对自定义字段中的唯一值进行不区分大小写的比较。

Answer 2

回答by Foo

Alternatively you can change the default Query Set Manager to do case insensitive look-ups on the field. In trying to solve a similar problem I came across:

或者，您可以更改默认的查询集管理器以对字段进行不区分大小写的查找。在尝试解决类似的问题时，我遇到了：

http://djangosnippets.org/snippets/305/

Code pasted here for convenience:

为方便起见，代码粘贴在此处：

from django.db.models import Manager
from django.db.models.query import QuerySet

class CaseInsensitiveQuerySet(QuerySet):
    def _filter_or_exclude(self, mapper, *args, **kwargs):
        # 'name' is a field in your Model whose lookups you want case-insensitive by default
        if 'name' in kwargs:
            kwargs['name__iexact'] = kwargs['name']
            del kwargs['name']
        return super(CaseInsensitiveQuerySet, self)._filter_or_exclude(mapper, *args, **kwargs)

# custom manager that overrides the initial query set
class TagManager(Manager):
    def get_query_set(self):
        return CaseInsensitiveQuerySet(self.model)

# and the model itself
class Tag(models.Model):
    name = models.CharField(maxlength=50, unique=True, db_index=True)

    objects = TagManager()

    def __str__(self):
        return self.name

Answer 3

回答by eyaler

Explicit steps for Mayuresh's answer:

Mayuresh 回答的明确步骤：

in postgres do: CREATE EXTENSION citext;

in your models.py add:

from django.db.models import fields

class CaseInsensitiveTextField(fields.TextField):
    def db_type(self, connection):
        return "citext"

reference: https://github.com/zacharyvoase/django-postgres/blob/master/django_postgres/citext.py

in your model use: name = CaseInsensitiveTextField(unique=True)

在 postgres 中做：CREATE EXTENSION citext;

在你的 models.py 添加：

from django.db.models import fields

class CaseInsensitiveTextField(fields.TextField):
    def db_type(self, connection):
        return "citext"

参考：https: //github.com/zacharyvoase/django-postgres/blob/master/django_postgres/citext.py

在您的模型中使用： name = CaseInsensitiveTextField(unique=True)

Answer 4

回答by Alex Brasetvik

On the Postgres side of things, a functional unique index will let you enforce unique values without case. citext is also noted, but this will work with older versions of PostgreSQL and is a useful technique in general.

在 Postgres 方面，功能唯一索引可以让您在不区分大小写的情况下强制执行唯一值。citext 也被指出，但这将适用于旧版本的 PostgreSQL，并且通常是一种有用的技术。

Example:

例子：

# create table foo(bar text);
CREATE TABLE
# create unique index foo_bar on foo(lower(bar));
CREATE INDEX
# insert into foo values ('Texas');
INSERT 0 1
# insert into foo values ('texas');
ERROR:  duplicate key value violates unique constraint "foo_bar"

Answer 5

回答by suhailvs

a very simple solution:

一个非常简单的解决方案：

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

    def clean(self):
        self.name = self.name.capitalize()

Answer 6

回答by Michal ?iha?

Besides already mentioned option to override save, you can simply store all text in lower case in database and capitalize them on displaying.

除了已经提到的覆盖保存的选项，您可以简单地将所有文本以小写形式存储在数据库中并在显示时将它们大写。

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

    def save(self, force_insert=False, force_update=False):
        self.name = self.name.lower()
        super(State, self).save(force_insert, force_update)

Answer 7

回答by Luan Francisco Lima Fernandes

You can use lookup='iexact' in UniqueValidator on serializer, like this:

您可以在序列化程序的 UniqueValidator 中使用 lookup='iexact' ，如下所示：

class StateSerializer(serializers.ModelSerializer): 
    name = serializers.CharField(validators=[
    UniqueValidator(
        queryset=models.State.objects.all(),lookup='iexact'
    )]

django version: 1.11.6

Django 版本：1.11.6

Answer 8

回答by Rishabh Manocha

You can do this by overwriting the Model's save method - see the docs. You'd basically do something like:

您可以通过覆盖模型的保存方法来做到这一点 - 请参阅文档。你基本上会做这样的事情：

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

    def save(self, force_insert=False, force_update=False):
        if State.objects.get(name__iexact = self.name):
            return
        else:
            super(State, self).save(force_insert, force_update)

Also, I may be wrong about this, but the upcoming model-validation SoC branch will allow us to do this more easily.

此外，我可能错了，但即将推出的模型验证 SoC 分支将使我们能够更轻松地做到这一点。

Answer 9

回答by Jorge

Solution from suhail worked for me without the need to enable citext, pretty easy solution only a clean function and instead of capitalize I used upper(). Mayuresh's solution also works but changed the field from CharFieldto TextField.

suhail 的解决方案对我有用，无需启用 citext，非常简单的解决方案只有一个干净的功能，而不是大写我使用upper(). Mayuresh 的解决方案也有效，但将字段从更改CharField为TextField。

class State(models.Model):

    name = models.CharField(max_length=50, unique=True)

    def clean(self):
        self.name = self.name.upper()

Answer 10

回答by Levi Payne

If you don't want to use a postgres-specific solution, you can create a unique index on the field with upper()to enforce uniqueness at the database level, then create a custom Fieldmixin that overrides get_lookup()to convert case-sensitive lookups to their case-insensitive versions. The mixin looks like this:

如果您不想使用特定于 postgres 的解决方案，您可以在字段上创建一个唯一索引，upper()以在数据库级别强制执行唯一性，然后创建一个自定义Fieldmixin 来覆盖get_lookup()以将区分大小写的查找转换为不区分大小写的查找版本。混合看起来像这样：

class CaseInsensitiveFieldMixin:
    """
    Field mixin that uses case-insensitive lookup alternatives if they exist.
    """

    LOOKUP_CONVERSIONS = {
        'exact': 'iexact',
        'contains': 'icontains',
        'startswith': 'istartswith',
        'endswith': 'iendswith',
        'regex': 'iregex',
    }

    def get_lookup(self, lookup_name):
        converted = self.LOOKUP_CONVERSIONS.get(lookup_name, lookup_name)
        return super().get_lookup(converted)

And you use it like this:

你像这样使用它：

from django.db import models


class CICharField(CaseInsensitiveFieldMixin, models.CharField):
    pass


class CIEmailField(CaseInsensitiveFieldMixin, models.EmailField):
    pass


class TestModel(models.Model):
    name = CICharField(unique=True, max_length=20)
    email = CIEmailField(unique=True)

You can read more about this approach here.

您可以在此处阅读有关此方法的更多信息。

postgresql Django 中的独特模型字段和区分大小写（postgres）

提问by chefsmart

回答by Mayuresh

回答by Foo

回答by eyaler

回答by Alex Brasetvik

回答by suhailvs

回答by Michal ?iha?

回答by Luan Francisco Lima Fernandes

回答by Rishabh Manocha

回答by Jorge

回答by Levi Payne

相关推荐

最近更新

标签

postgresql Django 中的独特模型字段和区分大小写（postgres）

提问by chefsmart

回答by Mayuresh

回答by Foo

回答by eyaler

回答by Alex Brasetvik

回答by suhailvs

回答by Michal ?iha?

回答by Luan Francisco Lima Fernandes

回答by Rishabh Manocha

回答by Jorge

回答by Levi Payne

相关推荐

postgresql Restlet 使用 json 接收和响应实现 post

postgresql 引用 PK 的外键是否需要 NOT NULL 约束？

postgresql PHP 和 Postgres：捕捉错误？

ogr2ogr 和 Postgis/PostgreSQL 数据库的编码问题

相关推荐

最近更新

标签