Fixing the GiST Index and Exclusion Constraint for DateTimeRangeField

The error you're encountering is related to the PostgreSQL GiST index configuration. The message indicates that PostgreSQL doesn't know how to create a GiST index for a bigint type, which is likely related to your related_property field in the exclusion constraint.

Issues and Solutions

There are a few issues with your current implementation:

1. You're using a GinIndex for a range field, but range fields work better with GistIndex
2. The exclusion constraint needs proper operator classes for both the range field and the foreign key
3. You need to install the btree_gist extension for the foreign key field to work with GiST

Here's how to fix these issues:

1. Change the Index Type

First, replace the GinIndex with a GistIndex:

PYTHONCopied
from django.contrib.postgres.indexes import GistIndex

# In your Meta class:
indexes = [
    GistIndex(
        fields=['client_checking_range'],
        name='booking_client_range_gist_idx'
    )
]

2. Fix the Exclusion Constraint

The main issue is with the exclusion constraint. You need to use the btree_gist extension to handle the related_property field (which is a foreign key to an integer). Here's the corrected constraint:

PYTHONCopied
from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import RangeOperators
from django.db.models import Q
from django.contrib.postgres.indexes import OpClass

constraints = [
    ExclusionConstraint(
        name="non_overlapped_reservation",
        expressions=[
            ("client_checking_range", RangeOperators.OVERLAPS),
            (OpClass("related_property", name="gist_int4_ops"), RangeOperators.EQUAL)
        ],
        condition=Q(status="Accepted") | Q(status="Paid") | Q(status="Pending"),
        index_type='GIST'  # Explicitly specify GIST index type
    )
]

3. Install the btree_gist Extension

You need to create a migration to install the btree_gist extension. Create a new migration file:

PYTHONCopied
from django.contrib.postgres.operations import BtreeGistExtension
from django.db import migrations

class Migration(migrations.Migration):
    operations = [
        BtreeGistExtension(),
    ]

Complete Solution

Here's the updated model with all the fixes:

PYTHONCopied
class Booking(BaseModel):
    # Your fields remain the same
    
    class Meta:
        verbose_name = _("Booking")
        verbose_name_plural = _("Bookings")

        indexes = [
            GistIndex(
                fields=['client_checking_range'],
                name='booking_client_range_gist_idx'
            )
        ]

        constraints = [
            ExclusionConstraint(
                name="non_overlapped_reservation",
                expressions=[
                    ("client_checking_range", RangeOperators.OVERLAPS),
                    (OpClass("related_property", name="gist_int4_ops"), RangeOperators.EQUAL)
                ],
                condition=Q(status="Accepted") | Q(status="Paid") | Q(status="Pending"),
                index_type='GIST'
            )
        ]

Don't forget to create and apply the migration for the btree_gist extension before applying your model migrations.

This approach should resolve the error you're encountering by properly configuring the GiST index for both the range field and the foreign key field.