Opened 2 years ago

Closed 2 years ago

#34078 closed New feature (wontfix)

Add Postgres range_merge aggregate function to postgres.contrib

Reported by: Jack Linke Owned by:
Component: contrib.postgres Version: 4.1
Severity: Normal Keywords: postgres range range_merge aggregation aggregate
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Most of the Postgres range functions have been exposed for querying in django, but not the range_merge Aggregate function.

As background, range_merge "Computes the smallest range that includes ... the given ranges".

The existing Postgres functions exposed for queries in django: https://docs.djangoproject.com/en/4.1/ref/contrib/postgres/fields/#querying-using-the-bounds

The full list of Posgres range functions (note that range_merge is the only one not represented in django): https://www.postgresql.org/docs/14/functions-range.html#RANGE-FUNCTIONS-TABLE


To make use of this aggregation in my own project, I created a django package that runs a migration to CREATE (or DROP) the aggregate function in the database (https://pypi.org/project/django-range-merge/).

operations = [
    migrations.RunSQL(
        sql=[("CREATE OR REPLACE AGGREGATE range_merge(anyrange)(sfunc=range_merge, stype=anyrange);")],
        reverse_sql=[("DROP AGGREGATE IF EXISTS range_merge(anyrange);")],
    )
]

Then, given the model:

class Event(models.Model):
    name = models.CharField(max_length=30)
    potential_visitors = models.IntegerRangeField()

And a few model instances:

{"id" : 1, "name" : "Birthday", "potential_visitors" : "[2, 3)"}
{"id" : 2, "name" : "Bake Sale", "potential_visitors" : "[30, 50)"}
{"id" : 3, "name" : "Band Camp", "potential_visitors" : "[22, 28)"}
{"id" : 4, "name" : "Cooking Show", "potential_visitors" : "[7, 20)"}
{"id" : 5, "name" : "Pajama Day", "potential_visitors" : "[15, 30)"}

Executing the query:

Event.objects.all().aggregate(
    output=Aggregate(F("potential_visitors"), function="range_merge")
)

Returns:

{'output': NumericRange(2, 50, '[)')}


It looks like RunSQL is not used anywhere in django's codebase, except in tests, so I am not sure if adding this to the contrib.postgres codebase would be welcomed.

Should this be added to django, or should it remain as a package separate from django itself?

Change History (3)

comment:1 by Jack Linke, 2 years ago

Summary: Should the Postgres range_merge aggregate be added to postgres.contrib?Add Postgres range_merge aggregate function to postgres.contrib

comment:2 by Claude Paroz, 2 years ago

I think adding the range_merge function could be accepted in contrib.postgres. However, what you suggest is to add a custom aggregate (which is a different variant of the range_agg/range_intersect_agg aggregations added in PostgreSQL 14). Therefore, my opinion is that your custom aggregation is quite niche and could stay in a custom package.

comment:3 by Mariusz Felisiak, 2 years ago

Resolution: wontfix
Status: newclosed

I agree with Claude. Marking of adding a custom aggregate function as wontfix.

Note: See TracTickets for help on using tickets.
Back to Top