[Django] #34701: Add support for NULLS [NOT] DISTINCT to UniqueConstraint
Django
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: nobody
Charette |
Type: New | Status: assigned
feature |
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
PostgresSQL 15 is one of the first databases to implement the F292 SQL
feature present in the 2023 standard that allows to specify whether or not
null values should be considered as distinct in unique constraints.
While most backends default to implicit `NULLS DISTINCT` some backends
don't, most notably SQL Server (cff59bedc23fd4d53557f677ddc42402b56963d0).
I suggest we add a `nulls_distinct: Optional[bool]=None` option to
`UniqueConstraint` so it preserves it's ''default to backend treatment of
nulls'' behaviour while allowing it to be set explicitly on backends that
support it.
- [https://modern-sql.com/caniuse/unique-nulls-not-distinct Article on the
subject]
- [https://modern-sql.com/caniuse/F292 SQL:F292]
- [https://forum.djangoproject.com/t/support-nulls-not-distinct-
postgres-15-clause/21973/3 Recent thread on the subject in the forums]
--
Ticket URL: <https://code.djangoproject.com/ticket/34701>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Replying to [ticket:34701 Simon Charette]:
> PostgresSQL 15 is one of the first databases to implement the F292 SQL
feature present in the 2023 standard that allows to specify whether or not
null values should be considered as distinct in unique constraints.
>
> While most backends default to implicit `NULLS DISTINCT` some backends
don't, most notably SQL Server (cff59bedc23fd4d53557f677ddc42402b56963d0).
>
> I suggest we add a `nulls_distinct:Optional[bool]=None` option to
`UniqueConstraint` so it preserves it's ''default to backend treatment of
nulls'' behaviour while allowing it to be set explicitly on backends that
support it.
>
> - [https://modern-sql.com/caniuse/unique-nulls-not-distinct Article on
the subject]
> - [https://modern-sql.com/caniuse/F292 SQL:F292]
> - [https://forum.djangoproject.com/t/support-nulls-not-distinct-
postgres-15-clause/21973/3 Recent thread on the subject in the forums]
--
Ticket URL: <https://code.djangoproject.com/ticket/34701#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: nobody
Type: New feature | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> PostgresSQL 15 is one of the first databases to implement the F292 SQL
> feature present in the 2023 standard that allows to specify whether or
> not null values should be considered as distinct in unique constraints.
>
> While most backends default to implicit `NULLS DISTINCT` some backends
> don't, most notably SQL Server
> (cff59bedc23fd4d53557f677ddc42402b56963d0).
>
> I suggest we add a `nulls_distinct: Optional[bool]=None` option to
> `UniqueConstraint` so it preserves it's ''default to backend treatment of
> nulls'' behaviour while allowing it to be set explicitly on backends that
> support it.
>
> - [https://modern-sql.com/caniuse/unique-nulls-not-distinct Article on
> the subject]
> - [https://modern-sql.com/caniuse/F292 SQL:F292]
> - [https://forum.djangoproject.com/t/support-nulls-not-distinct-
> postgres-15-clause/21973/3 Recent thread on the subject in the forums]
New description:
Django
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: nobody
Type: New feature | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/34701#comment:2>
Django
-------------------------------------+-------------------------------------
| Charette
Type: New feature | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Simon Charette
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/34701#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: New feature | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/34701#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"595a2abb58e04caa4d55fb2589bb80fb2a8fdfa1" 595a2ab]:
{{{
#!CommitTicketReference repository=""
revision="595a2abb58e04caa4d55fb2589bb80fb2a8fdfa1"
Fixed #34701 -- Added support for NULLS [NOT] DISTINCT on PostgreSQL 15+.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34701#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: New feature | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
In [changeset:"0e49a8c3bd9119795525d9f076f73740741479b7" 0e49a8c3]:
{{{#!CommitTicketReference repository=""
revision="0e49a8c3bd9119795525d9f076f73740741479b7"
Refs #34701 -- Moved UniqueConstraint(nulls_distinct) validation tests.
The original tests required the creation of a model that is no longer
necessary
and were exercising Model.full_clean(validate_constraints) which has
nothing
to do with the nulls_distinct feature.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34701#comment:6>