Support PostgreSQL 18+ temporal constraints

[Adam Johnson]

• ​Release note
• ​`UNIQUE CONSTRAINT` docs for `WITHOUT OVERLAPS`
• ​`PRIMARY KEY` docs for `WITHOUT OVERLAPS`
• ​`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.

[Simon Charette]

If we are to accept this ticket I suggest we focus it on the UniqueConstraint case and then consider CompositePrimaryKey and foreign support once #35956 lands.

The hard part here will likely not be to write the WithoutOverlaps expression part but to adjust UniqueConstraint.validate to honor it without coupling contrib.postgres and core.

I'll note that WITHOUT OVERLAPS is really just SQL sugar over exclusion constraints which we already support via ExclusionConstraint so I'm not entirely convinced it's worth adding given the UniqueConstraint.validate complexity it would incur. If it was supported by more than one supported engine then it could be worthy but AFAIK it's not the case.

[Adam Johnson]

Yes, it seems to be PostgreSQL-only. But the SQL syntax *is* much easier to use, and it would be similarly so within Django models, which should help with adoption of exclusion/temporal constraints.

[Jacob Walls]

Thanks all, please continue this discussion at django/new-features. Any level of effort estimate you would have on UniqueConstraint.validate and Simon's concern about contrib.postgres-specific flavors of UniqueConstraint would be helpful.

[David Sanders]

I haven't seen the new-features thread for this? so adding my 2¢ here for now...

I'd love to see temporal features be added to Django, I've been toying with this myself as well.

It should be noted that MariaDB's temporal features are more feature complete and differ slightly as they tend more towards the SQL standard. If we do support temporal features, then I'd strongly urge folks to, at least, be sure to include Maria in design considerations, (otherwise centre design around this owing to the closeness of the standard?)

​https://mariadb.com/docs/server/reference/sql-structure/temporal-tables

Note the key difference is the formation of "periods" from date/timestamp columns from which the "without overlaps" keys are formed.

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE,
 PERIOD FOR p(checkin,checkout),
 UNIQUE (room_number, p WITHOUT OVERLAPS)
 );

Adam if you want to create the new-feature I'll gladly upvote it