#36627: Support PostgreSQL 18+ temporal constraints
------------------------------+--------------------------------------------
Reporter: Adam Johnson | Type: Uncategorized
Status: new | Component: contrib.postgres
Version: dev | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
------------------------------+--------------------------------------------
-
[https://www.postgresql.org/about/news/postgresql-18-released-3142/#:~:text=PostgreSQL%2018%20adds%20temporal%20constraints
Release note]
- [https://www.postgresql.org/docs/18/sql-createtable.html#SQL-
CREATETABLE-PARMS-UNIQUE:~:text=If%20the%20WITHOUT%20OVERLAPS%20option
`UNIQUE CONSTRAINT` docs for `WITHOUT OVERLAPS`]
- [https://www.postgresql.org/docs/18/sql-createtable.html#SQL-
CREATETABLE-PARMS-
UNIQUE:~:text=KEY%20%28%20column%5Fname%20%5B%2C%20%2E%2E%2E%20%5D%20%5B%2C%20column%5Fname-,WITHOUT%20OVERLAPS,-%5D%20%29%20%5B%20INCLUDE
`PRIMARY KEY` docs for `WITHOUT OVERLAPS`]
- [https://www.postgresql.org/docs/18/sql-createtable.html#SQL-
CREATETABLE-PARMS-
UNIQUE:~:text=If%20the%20last%20column%20is%20marked%20with%20PERIOD
`FOREIGN KEY` docs for `PERIOD`]
PostgreSQL 18 comes with support for "temporal constraints". These allow
adding a special modifier to the final column in a unique constraint
(`WITHOUT OVERLAPS`), primary key (`WITHOUT OVERLAPS`), or foreign key
(`PERIOD`) which turns that column into a kind of "period specifier". This
allows enforcing the given constraint is valid only for the range of that
period. While the feature is not time-specific, the natural use case is to
enforce uniqueness / validness over time, hence the name.
I propose that we add support for this feature in Django's
`UniqueConstraint`, through some a new expression `WithoutOverlaps` in
`django.contrib.postgres`, which would be used like this:
{{{
from django.db import models
from django.contrib.postgres.constraints import WithoutOverlaps
from django.contrib.postgres.fields import DateTimeRangeField
class Booking(models.Model):
title = models.TextField()
room = models.ForeignKey(Room, on_delete=models.CASCADE)
span = DateTimeRangeField()
class Meta:
constraints = [
models.UniqueConstraint(
"room",
WithoutOverlaps("span"),
name="%(app_label)s_%(class)s_room_span_no_overlaps"
),
]
}}}
This could compile down to SQL like `UNIQUE (room_id, span WITHOUT
OVERLAPS)`.
It may also be possible, but more tricky, to support the other two ways to
specify temporal constraints:
* Primary key support would need to modify or subclass
`CompositePrimaryKey` to support `WithoutOverlaps` for the final column.
* Foreign key support would need to modify or subclass `ForeignObject`,
which is still a private API.
--
Ticket URL: <https://code.djangoproject.com/ticket/36627>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019988157977-50748b7e-4dea-41e9-8e77-e07e78b6102f-000000%40eu-central-1.amazonses.com.
