unique indexes (for unique_together) on nullable columns need to be optionable for nulls not distinct about tortoise-orm HOT 3 OPEN

Hi!

The fact that it does not use nulls not distinct is intentional (as much intentional it can be for the case of default behaviour)

And I am pretty sure it shouldn't be default behaviour

Although adding support would be reasonable, I think it should be done by allowing it as param in tortoise.contrib.postgres.indexes.PostgreSQLIndex, not by changing behaviour in unique_together

You can contribute this change in PR, or may be I'll be able to take it on somewhere later

I think you can bypass this problem pretty easily by modifying your migration scripts if you have one, or creating index manually if you don't use migration system, as indexes declared on models doesn't affect runtime behaviour

from tortoise-orm.

Hi
that was great idea. by changes at this PR i solved my problem with following approach:

from tortoise... import PostgresUniqueIndex as PUI, SqliteUniqueIndex as SUI

def unique_index_depends_on_current_db_type(fields, null_fields):
  """this function just support sqlite/postgres unique index and nulls will be consider equal"""
  is_postgres = envs.db_url.lower().startswith('postgres')
  unique_index_class = PUI if is_postgres else SUI
  return unique_index_class(fields=fields, nulls='not distinct' if is_postgres else null_fields)

class Customer(BaseModel):
  class Meta:
    indexes = [unique_index_depends_on_current_db_type(fields=['shop_id','phone_number', 'deleted_at'], null_fields=['deleted_at'])]

from tortoise-orm.

another solution is using nonnull field

class BaseModel(Model):
  created_at = fields.DateTimeField(auto_now_add=True)
  updated_at = fields.DateTimeField(auto_now=True)
  is_deleted = fields.BooleanField(default=False)

from tortoise-orm.