tortoise/tortoise-orm

unique indexes (for unique_together) on nullable columns need to be optionable for nulls not distinct

Open

#1,641 opened on Jun 6, 2024

View on GitHub
 (3 comments) (0 reactions) (0 assignees)Python (333 forks)batch import
enhancementgood first issue

Repository metrics

Stars
 (3,863 stars)
PR merge metrics
 (Avg merge 15d 7h) (9 merged PRs in 30d)

Description

assume that i have two models shop, customer. each shop can have only one customer with an unique phone number till deletes it. please pay attention that customer's phone_number is not unique in column. that is unique together with its shop and delete state.

from tortoise import Model, fields

class Base(Model):
  deleted_at = fields.DateTimeField(null=True)
  class Meta:
    abstract = True

class Shop(Base):
  title = fields.CharField(...)

class Customer(Base):
  phone_number = fields.CharField(...) # not used unique here as you expect
  shop = fields.ForeignKeyField('models.Shop', 'customers', fields.CASCADE)
  class Meta:
    unique_together = ('shop_id', 'phone_number', 'deleted_at')

but this simple code is not work as i expected in postgres that i tested, created index did'nt follow by nulls not distinct when not follow. unique index considers nulls not equal. tortoise version : 0.21.3

Contributor guide

Research direction
Investigate how Tortoise ORM generates unique constraints for unique together, focusing on PostgreSQL index creation with nullable columns. Look into adding the NULLS NOT DISTINCT option to the generated SQL index.
Tech stack
python
Domain
backenddatabase
Issue type
Bug
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
2
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
1-3 hours
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
Active
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
Mostly clear
Prerequisites
GitPythonPostgreSQL
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
70