Bug UniqueConstraint with condition and nulls-distinct

[Lucas Lemke]

Hi, I`m Lucas (​https://github.com/lsaunitti)

I found a bug when set a UniqueConstrain using condition using nulls_distinct using like that:

Screenshot 2024-03-25 at 10.47.59.png

When django generate SQL to create a check constraint the result is ... WHERE <condition> NULLS NOT DISTINCT.
It raise an exception on Postgresql.

To fix it, I suggest change the file django/db/backends/base/schema.py on line 132:

Today:

    sql_create_unique_index = (
        "CREATE UNIQUE INDEX %(name)s ON %(table)s "
        "(%(columns)s)%(include)s%(condition)s%(nulls_distinct)s"
    )

To:

    sql_create_unique_index = (
        "CREATE UNIQUE INDEX %(name)s ON %(table)s "
        "(%(columns)s)%(include)s%(nulls_distinct)s%(condition)s"
    )

Regards,
Lucas Lemke Saunitti
Software Engineer

[Simon Charette]

Thank you for your report Lucas!

Marking as a release blocker since nulls_distinct is a new feature introduced in Django 5.0.

​The Postgres docs clearly point that NULLS DISTINCT should come before WHERE, sorry for missing that.

Would you be interested in submitting a PR with the proposed changes? Adding a test should be as simple as taking inspiration from the ones ​introduced when the feature was added.