Django ORM PostgreSQL

Django ORM PostgreSQL

Explore django orm postgresql code snippets and tutorials

Django ORM PostgreSQL

Bitwise aggregates (BitAnd, BitOr, BitXor over flags=1..10)

Fold an integer column with bitwise operators across each group: BitOr sets a bit if *any* row has it, BitAnd only if *all* do, and BitXor toggles.

python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# ======================================================================
# Fold an integer column with bitwise operators across each group: `BitOr` sets
# a bit if *any* row has it, `BitAnd` only if *all* do, and `BitXor` toggles.
# Handy for packed flag columns.
# ======================================================================

# --- Django ORM ---
Measurement.objects.values("series").annotate(
    b_and=BitAnd("flags"),
    b_or=BitOr("flags"),
    b_xor=BitXor("flags"),
).order_by("series")

# --- generated SQL ---
#   SELECT "examples_measurement"."series" AS "series", BIT_AND("examples_measurement"."flags") AS "b_and", BIT_OR("examples_measurement"."flags") AS "b_or", BIT_XOR("examples_measurement"."flags") AS "b_xor"
#   FROM "examples_measurement"
#   GROUP BY 1
#   ORDER BY 1 ASC

# ----------------------------------------------------------------------
# series 'linear'
# ----------------------------------------------------------------------

# Result:
#   bit_and=0 (0b0000)  bit_or=15 (0b1111)  bit_xor=11 (0b1011)

# ----------------------------------------------------------------------
# series 'noisy'
# ----------------------------------------------------------------------

# Result:
#   bit_and=0 (0b0000)  bit_or=15 (0b1111)  bit_xor=11 (0b1011)
May 22, 2026 python
Django ORM PostgreSQL

Statistical aggregates (Corr, RegrSlope, RegrIntercept, RegrR2, ...)

PostgreSQL's linear-regression aggregates.

python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
# ======================================================================
# PostgreSQL's linear-regression aggregates. `RegrSlope`/`RegrIntercept` fit y =
# slope·x + intercept, `Corr` is the correlation (−1..1), and `RegrR2` is the
# goodness of fit. Note the keyword form `y=`, `x=` (dependent variable first,
# as in SQL).
# ======================================================================

# --- Django ORM ---
Measurement.objects.values("series").annotate(
    n=RegrCount(y="y", x="x"),
    corr=Corr(y="y", x="x"),
    slope=RegrSlope(y="y", x="x"),
    intercept=RegrIntercept(y="y", x="x"),
    r2=RegrR2(y="y", x="x"),
    cov=CovarPop(y="y", x="x"),
    avg_x=RegrAvgX(y="y", x="x"), avg_y=RegrAvgY(y="y", x="x"),
    sxx=RegrSXX(y="y", x="x"), syy=RegrSYY(y="y", x="x"), sxy=RegrSXY(y="y", x="x"),
).order_by("series")

# --- generated SQL ---
#   SELECT "examples_measurement"."series" AS "series", REGR_COUNT("examples_measurement"."y", "examples_measurement"."x") AS "n", CORR("examples_measurement"."y", "examples_measurement"."x") AS "corr", REGR_SLOPE("examples_measurement"."y", "examples_measurement"."x") AS "slope", REGR_INTERCEPT("examples_measurement"."y", "examples_measurement"."x") AS "intercept", REGR_R2("examples_measurement"."y", "examples_measurement"."x") AS "r2", COVAR_POP("examples_measurement"."y", "examples_measurement"."x") AS "cov", REGR_AVGX("examples_measurement"."y", "examples_measurement"."x") AS "avg_x", REGR_AVGY("examples_measurement"."y", "examples_measurement"."x") AS "avg_y", REGR_SXX("examples_measurement"."y", "examples_measurement"."x") AS "sxx", REGR_SYY("examples_measurement"."y", "examples_measurement"."x") AS "syy", REGR_SXY("examples_measurement"."y", "examples_measurement"."x") AS "sxy"
#   FROM "examples_measurement"
#   GROUP BY 1
#   ORDER BY 1 ASC

# ----------------------------------------------------------------------
# series 'linear' (n=10)
# ----------------------------------------------------------------------

# Result:
#   fit:  y = 1.994 * x + 1.033
#   corr=1.000  r2=1.000  covar_pop=16.450
#   avg_x=5.50  avg_y=12.00
#   sxx=82.500  syy=328.100  sxy=164.500  (slope = sxy/sxx = 1.994)

# ----------------------------------------------------------------------
# series 'noisy' (n=10)
# ----------------------------------------------------------------------

# Result:
#   fit:  y = -0.018 * x + 4.800
#   corr=-0.020  r2=0.000  covar_pop=-0.150
#   avg_x=5.50  avg_y=4.70
#   sxx=82.500  syy=68.100  sxy=-1.500  (slope = sxy/sxx = -0.018)
May 22, 2026 python
Django ORM PostgreSQL

Window functions — the full set

Every PostgreSQL window function Django wraps.

python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# ======================================================================
# Every PostgreSQL window function Django wraps. `RowNumber` numbers rows,
# `PercentRank`/`CumeDist` give relative position, `Ntile` buckets, `Lead` peeks
# at the next row, and `FirstValue`/`LastValue`/`NthValue` read specific rows in
# the window. `LastValue`/`NthValue` need an explicit `frame=RowRange(None,
# None)` to see the whole partition, not just up to the current row. A custom
# frame also powers the running total.
# ======================================================================

# ----------------------------------------------------------------------
# ranking & value functions
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(
    rownum=Window(RowNumber(), order_by=F("price").desc()),
    pct=Window(PercentRank(), order_by=F("price").desc()),
    cume=Window(CumeDist(), order_by=F("price").desc()),
    half=Window(Ntile(num_buckets=2), order_by=F("price").desc()),
    nxt=Window(Lead("price"), order_by=F("price").desc()),
    first=Window(FirstValue("title"), order_by=F("price").desc()),
    last=Window(LastValue("title"), order_by=F("price").desc(),
                frame=RowRange(start=None, end=None)),
    second=Window(NthValue("title", nth=2), order_by=F("price").desc(),
                  frame=RowRange(start=None, end=None)),
).order_by("-price")

# SQL:
#   SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper", ROW_NUMBER() OVER (ORDER BY "examples_book"."price" DESC) AS "rownum", PERCENT_RANK() OVER (ORDER BY "examples_book"."price" DESC) AS "pct", CUME_DIST() OVER (ORDER BY "examples_book"."price" DESC) AS "cume", NTILE(2) OVER (ORDER BY "examples_book"."price" DESC) AS "half", LEAD("examples_book"."price", 1) OVER (ORDER BY "examples_book"."price" DESC) AS "nxt", FIRST_VALUE("examples_book"."title") OVER (ORDER BY "examples_book"."price" DESC) AS "first", LAST_VALUE("examples_book"."title") OVER (ORDER BY "examples_book"."price" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "last", NTH_VALUE("examples_book"."title", 2) OVER (ORDER BY "examples_book"."price" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "second"
#   FROM "examples_book"
#   ORDER BY "examples_book"."price" DESC

# Result:
#   #1 The Lord of the Rings: pct=0.00 cume=0.25 ntile=1 next=$19.99 first='The Lord of the Rings' last='Postgres for Authors' 2nd='Harry Potter'
#   #2 Harry Potter: pct=0.33 cume=0.50 ntile=1 next=$14.50 first='The Lord of the Rings' last='Postgres for Authors' 2nd='Harry Potter'
#   #3 The Hobbit: pct=0.67 cume=0.75 ntile=2 next=$0.00 first='The Lord of the Rings' last='Postgres for Authors' 2nd='Harry Potter'
#   #4 Postgres for Authors: pct=1.00 cume=1.00 ntile=2 next=$None first='The Lord of the Rings' last='Postgres for Authors' 2nd='Harry Potter'

# ----------------------------------------------------------------------
# running total (custom frame: UNBOUNDED PRECEDING .. CURRENT ROW)
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(
    running=Window(Sum("price"), order_by=F("published").asc(),
                   frame=RowRange(start=None, end=0)),
).order_by("published")

# SQL:
#   SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper", SUM("examples_book"."price") OVER (ORDER BY "examples_book"."published" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "running"
#   FROM "examples_book"
#   ORDER BY "examples_book"."published" ASC

# Result:
#   The Hobbit ($14.50) -> running total $14.50
#   The Lord of the Rings ($29.00) -> running total $43.50
#   Harry Potter ($19.99) -> running total $63.49
#   Postgres for Authors ($0.00) -> running total $63.49
May 22, 2026 python
Django ORM PostgreSQL

Window functions (Rank, DenseRank, Lag over price)

A Window keeps every row but computes across a sliding 'window' defined by order_by and partition_by.

python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# ======================================================================
# A `Window` keeps every row but computes across a sliding 'window' defined by
# `order_by` and `partition_by`. `Rank` leaves gaps after ties, `DenseRank` does
# not, and `Lag` reads the previous row's value — none of this collapses the
# result set the way an aggregate would.
# ======================================================================

# ----------------------------------------------------------------------
# books ranked by price
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(
    price_rank=Window(Rank(), order_by=F("price").desc()),
    dense=Window(DenseRank(), order_by=F("price").desc()),
    prev=Window(Lag("price"), order_by=F("price").desc()),
).order_by("-price")

# SQL:
#   SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper", RANK() OVER (ORDER BY "examples_book"."price" DESC) AS "price_rank", DENSE_RANK() OVER (ORDER BY "examples_book"."price" DESC) AS "dense", LAG("examples_book"."price", 1) OVER (ORDER BY "examples_book"."price" DESC) AS "prev"
#   FROM "examples_book"
#   ORDER BY "examples_book"."price" DESC

# Result:
#   rank 1 (dense 1): The Lord of the Rings $29.00  prev=$None
#   rank 2 (dense 2): Harry Potter $19.99  prev=$29.00
#   rank 3 (dense 3): The Hobbit $14.50  prev=$19.99
#   rank 4 (dense 4): Postgres for Authors $0.00  prev=$14.50
May 22, 2026 python
Django ORM PostgreSQL

PostgreSQL-only aggregates (ArrayAgg, StringAgg, JSONBAgg, BoolAnd/Or)

These aggregates exist only on PostgreSQL.

python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# ======================================================================
# These aggregates exist only on PostgreSQL. `ArrayAgg` gathers a column into an
# array, `StringAgg` joins it into one delimited string, `JSONBAgg` builds a
# JSON array, and `BoolAnd`/`BoolOr` reduce booleans. `default=Value([])` keeps
# authors with no books from collapsing to NULL.
# ======================================================================

# --- Django ORM ---
Author.objects.annotate(
    titles=ArrayAgg("books__title", distinct=True, default=Value([])),
    csv=StringAgg("books__title", delimiter=", ", default=Value("")),
    as_json=JSONBAgg("books__title", default=Value([])),
    all_active=BoolAnd("books__is_active"),
    any_active=BoolOr("books__is_active"),
    n=Count("books"),
)

# --- generated SQL ---
#   SELECT "examples_author"."id", "examples_author"."name", "examples_author"."bio", "examples_author"."born", "examples_author"."rating", "examples_author"."nickname", COALESCE(ARRAY_AGG(DISTINCT "examples_book"."title" ), '{}'::varchar(200)[]) AS "titles", COALESCE(STRING_AGG("examples_book"."title", ', ' ), '') AS "csv", COALESCE(JSONB_AGG("examples_book"."title" ), '[]'::jsonb) AS "as_json", BOOL_AND("examples_book"."is_active") AS "all_active", BOOL_OR("examples_book"."is_active") AS "any_active", COUNT("examples_book"."id") AS "n"
#   FROM "examples_author"
#   LEFT OUTER JOIN "examples_book" ON ("examples_author"."id" = "examples_book"."author_id")
#   GROUP BY "examples_author"."id"

# ----------------------------------------------------------------------
# J. K. Rowling (1 book(s))
# ----------------------------------------------------------------------

# Result:
#   titles (ArrayAgg): ['Harry Potter']
#   csv (StringAgg): Harry Potter
#   json (JSONBAgg): ['Harry Potter']
#   all_active=True  any_active=True

# ----------------------------------------------------------------------
# J. R. R. Tolkien (2 book(s))
# ----------------------------------------------------------------------

# Result:
#   titles (ArrayAgg): ['The Hobbit', 'The Lord of the Rings']
#   csv (StringAgg): The Hobbit, The Lord of the Rings
#   json (JSONBAgg): ['The Hobbit', 'The Lord of the Rings']
#   all_active=True  any_active=True

# ----------------------------------------------------------------------
# Karl Müller (1 book(s))
# ----------------------------------------------------------------------

# Result:
#   titles (ArrayAgg): ['Postgres for Authors']
#   csv (StringAgg): Postgres for Authors
#   json (JSONBAgg): ['Postgres for Authors']
#   all_active=True  any_active=True
May 22, 2026 python