# Proposal: Add generate\_series() support to contrib.postgres?

**URL:** <https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947>\
**Category:** ORM\
**Created:** [June 29, 2023, 1:01pm UTC](https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947 "2023-06-29T13:01:34Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![shangxiao](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/shangxiao/32/10313_2.png) [@shangxiao](https://forum.djangoproject.com/u/shangxiao)\
**Post date:** [June 29, 2023, 1:01pm UTC](https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947/1 "2023-06-29T13:01:34Z")

</div>

Hi folks,

This one’s a long shot but thought I might as well throw the idea out there:

I often make use of Postgres’ generate\_series() to generate reports aggregating data against a date range in buckets of intervals like hourly, weekly, monthly etc.

You can normally aggregate in intervals like hourly, weekly etc if you can transform timestamps to match the interval and then do group by on that transform but generate\_series() improves this in 2 ways:

- you can generate aggregates for intervals for which there’s no easy way to determine what interval the timestamp is in eg 5 min or fortnightly intervals (ie there’s no “date\_trunc()” / “extract(‘week’)” equivalent)
- you can aggregate densely stored data and still generate intervals for the gaps between your data. generate\_series() is often used as a left/right join in order to generate the nulls/zeroes.

Unfortunately the only way to use generate\_series() right now is with raw SQL. I don’t mind SQL but deviating from the standard queryset code has its disadvantages.

I’ve been playing around with the idea of creating a GenerateSeries expression that you can annotate to a queryset and just used it successfully to generate a 12 month report with aggregates per month. It made the code a lot more concise & easier to read.

Here’s what I came up with so far: [https://github.com/shangxiao/stupid-django-tricks/tree/master/custom\_joins](https://github.com/shangxiao/stupid-django-tricks/tree/master/custom_joins)

Basic usage is like so:

```auto
class Data(Model):
    timestamp = DateTimeField()
    data = IntegerField()

series = GenerateSeries(
    start=datetime(2000, 1, 1, tzinfo=utc), # start and stop can also be expressions so that you can date_trunc() if need be!
    stop=datetime(2000, 1, 5, tzinfo=utc),
    step=timedelta(days=1),
    join_condition=Q(
        timestamp__gte=SeriesRef(), # a ref is required in order to allow the join to refer back to the series and define the interval boundaries
        timestamp__lte=SeriesRef() + Value(timedelta(days=1)),
    ),
    alias="series",
)

dataset = (
    Data.objects.annotate(series)
    .values("series")
    .annotate(sum=Coalesce(Sum("data"), 0))
    .values("series", "sum")
    .order_by("series")
)

```

Anyone else think generate\_series() would be useful to have?

Edit: I just want to point out the solution above isn’t perfect. To get the resultset to have types of `timestamp with timezone` you must select the series like so: `"series"."timestamptz"`. I couldn’t see any way to do this without adding a small RawSQL annotation to alias result.

---

<div class="post-metadata">

**Author:** ![adamchainz](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/adamchainz/32/26_2.png) [@adamchainz](https://forum.djangoproject.com/u/adamchainz)\
**Post date:** [July 3, 2023, 10:21am UTC](https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947/2 "2023-07-03T10:21:23Z")

</div>

+1 This would indeed be useful. We should aim for a more general-purpose virtual table capability, to allow for use of other databases’ equivalents.

Matthew Schinckel wrote a post about a custom class to make `generate_series` work in 2019: [Functions as Tables in Django and Postgres - Schinckel.net](https://schinckel.net/2019/10/31/functions-as-tables-in-django-and-postgres/)

You’ve come up with better syntax, but maybe it could be iterated on further, especially if we can modify the ORM.

---

<div class="post-metadata">

**Author:** ![shangxiao](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/shangxiao/32/10313_2.png) [@shangxiao](https://forum.djangoproject.com/u/shangxiao)\
**Post date:** [July 10, 2023, 8:47am UTC](https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947/3 "2023-07-10T08:47:56Z")

</div>

Nice, well it’s good to see others also interested in supporting generate\_series() - whether that’s generic or specific.

Generic support for virtual tables would be awesome but it does sounds like a big rabbit hole we could get into there. It’s easy enough to define a model backed by a virtual table but coming up with a way to join to models on-the-fly seems like the tricky part. There are probably a bunch of other problems I haven’t thought of yet too.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [July 12, 2023, 2:52am UTC](https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947/4 "2023-07-12T02:52:27Z")

</div>

Hello David!

As Adam alluded at I think the bits that Django should focus on is providing the proper primitives for table-like and _set returning_ expressions to be inserted and referenced through the ORM.

`generate_series` is an example of such expressions but `unnest` and the more commonly requested and database agnostic `Subquery` is another example of that.

If we put the problem of composite output fields aside for a moment (expressions that can return _rows_) I think that good first step would be to add an `Expression` flag that identify which expressions are _set returning_ and use `Subquery` as the default one to validate the approach.

With this new metadata available we could deprecate `FilteredRelation` in favour of a more generic `Relation` expression that accepts either a resolvable relation name (like `FilteredRelation` does) or a _set returning_ expression as it’s first argument.

It could then be used as

```python
series = GenerateSeries(
    start=datetime(2000, 1, 1, tzinfo=utc),
    stop=datetime(2000, 1, 5, tzinfo=utc),
    step=timedelta(days=1)
)
Data.objects.alias(
    series=Relation(
        series,
        condition=Q(
            series__between=(...)
        )
    )
)

```

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [July 10, 2024, 12:56am UTC](https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947/5 "2024-07-10T00:56:21Z")

</div>

Trying to encourage discussions on a potential way to add support for set-returning (table-valued) functions to the ORM in [this ticket](https://code.djangoproject.com/ticket/35586) is you’re still eager to see it land @shangxiao.

---

<div class="post-metadata">

**Author:** ![shangxiao](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/shangxiao/32/10313_2.png) [@shangxiao](https://forum.djangoproject.com/u/shangxiao)\
**Post date:** [July 10, 2024, 7:03am UTC](https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947/6 "2024-07-10T07:03:54Z")

</div>

Thanks Simon ❤

I’m just coming down off the “all hands on deck” mode I’ve been on over the past 2 months and am keen to revisit some of the things I’ve left off - this included (very timely that you pinged me).

One of the things I’ve recently wanted to add to Django is the ability to have `LATERAL JOIN` as a way to extend on the `SubQuery()` scalar subquery select capability - as lateral joins offer the ability to return entire records and not just scalars.

I’ll read through that ticket you linked this evening 👍

---

<div class="post-metadata">

**Author:** ![sonianuj287](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/sonianuj287/32/31808_2.png) [@sonianuj287](https://forum.djangoproject.com/u/sonianuj287)\
**Post date:** [March 8, 2026, 11:25am UTC](https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947/7 "2026-03-08T11:25:46Z")

</div>

I’ve started implementing a prototype and opened a PR for discussion

---

<div class="post-metadata">

**Author:** ![Pravin](https://avatars.discourse-cdn.com/v4/letter/p/b38774/32.png) [@Pravin](https://forum.djangoproject.com/u/Pravin)\
**Post date:** [May 22, 2026, 3:09am UTC](https://forum.djangoproject.com/t/proposal-add-generate-series-support-to-contrib-postgres/21947/8 "2026-05-22T03:09:12Z")

</div>

Created ticket [#37115 (Title: Add support for Generic Relations / Table-Valued Expressions in the ORM) – Django](https://code.djangoproject.com/ticket/37115)  
would appreciate eye on the process 🙂