Django ORM PostgreSQL

Django ORM PostgreSQL

Explore django orm postgresql code snippets and tutorials

Django ORM PostgreSQL

JSON builders, DISTINCT ON, collation & generated columns

Odds and ends unique to PostgreSQL: JSONObject/JSONArray build JSON from columns; .distinct('author') is SQL DISTINCT ON (one row per author); a non-deterministic db_collation makes nickname comparisons case-insensitive; and a GeneratedField …

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
# ======================================================================
# Odds and ends unique to PostgreSQL: `JSONObject`/`JSONArray` build JSON from
# columns; `.distinct('author')` is SQL `DISTINCT ON` (one row per author); a
# non-deterministic `db_collation` makes `nickname` comparisons case-
# insensitive; and a `GeneratedField` (`title_upper`) is computed and stored by
# the database on every write.
# ======================================================================

# ----------------------------------------------------------------------
# JSONObject builds a JSON object per row
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(obj=JSONObject(title="title", price="price")).values_list("title", "obj")

# SQL:
#   SELECT "examples_book"."title" AS "title", JSON_OBJECT((('title')::text) VALUE "examples_book"."title", (('price')::text) VALUE "examples_book"."price"
#   RETURNING JSONB) AS "obj"
#   FROM "examples_book"

# Result:
#   Harry Potter: {'price': 19.99, 'title': 'Harry Potter'}
#   The Hobbit: {'price': 14.5, 'title': 'The Hobbit'}
#   The Lord of the Rings: {'price': 29.0, 'title': 'The Lord of the Rings'}
#   Postgres for Authors: {'price': 0.0, 'title': 'Postgres for Authors'}

# ----------------------------------------------------------------------
# JSONArray builds a JSON array per row
# ----------------------------------------------------------------------

# Django:
Author.objects.annotate(arr=JSONArray("name", "rating")).values_list("name", "arr")

# SQL:
#   SELECT "examples_author"."name" AS "name", JSON_ARRAY(("examples_author"."name")::varchar(100), ("examples_author"."rating")::double precision NULL ON NULL
#   RETURNING JSONB) AS "arr"
#   FROM "examples_author"

# Result:
#   J. K. Rowling: ['J. K. Rowling', 4.7]
#   J. R. R. Tolkien: ['J. R. R. Tolkien', 4.9]
#   Karl Müller: ['Karl Müller', 3.8]

# ----------------------------------------------------------------------
# DISTINCT ON: highest-priced book per author
# ----------------------------------------------------------------------

# Django:
Book.objects.order_by("author", "-price").distinct("author") \
    .values_list("author__name", "title", "price")

# SQL:
#   SELECT DISTINCT ON ("examples_book"."author_id") "examples_author"."name" AS "author__name", "examples_book"."title" AS "title", "examples_book"."price" AS "price"
#   FROM "examples_book"
#   INNER JOIN "examples_author" ON ("examples_book"."author_id" = "examples_author"."id")
#   ORDER BY "examples_book"."author_id" ASC, 3 DESC

# Result:
#   J. K. Rowling: Harry Potter ($19.99)
#   J. R. R. Tolkien: The Lord of the Rings ($29.00)
#   Karl Müller: Postgres for Authors ($0.00)

# ----------------------------------------------------------------------
# case-insensitive db_collation match
# ----------------------------------------------------------------------

# Django:
Author.objects.filter(nickname="JO")   # matches stored "Jo"

# SQL:
#   SELECT "examples_author"."id", "examples_author"."name", "examples_author"."bio", "examples_author"."born", "examples_author"."rating", "examples_author"."nickname"
#   FROM "examples_author"
#   WHERE "examples_author"."nickname" = 'JO'

# Result:
#   nickname='JO' matched J. K. Rowling (stored as 'Jo')

# ----------------------------------------------------------------------
# GeneratedField title_upper (stored, computed by PG)
# ----------------------------------------------------------------------

# Django:
Book.objects.values_list("title", "title_upper")

# SQL:
#   SELECT "examples_book"."title" AS "title", "examples_book"."title_upper" AS "title_upper"
#   FROM "examples_book"

# Result:
#   Harry Potter -> HARRY POTTER
#   The Hobbit -> THE HOBBIT
#   The Lord of the Rings -> THE LORD OF THE RINGS
#   Postgres for Authors -> POSTGRES FOR AUTHORS
May 22, 2026 python
Django ORM PostgreSQL

JSONField lookups (nested key, contains, has_key)

JSONField stores jsonb.

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
59
60
61
62
63
64
65
66
67
68
69
70
# ======================================================================
# `JSONField` stores `jsonb`. Traverse nested keys with `__`
# (`data__specs__format`), test containment with `__contains` (`@>`), and key
# presence with `__has_key` (`?`). GIN indexes accelerate the containment
# lookups.
# ======================================================================

# ----------------------------------------------------------------------
# data.specs.format = 'hardcover'
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(data__specs__format="hardcover")

# 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"
#   FROM "examples_book"
#   WHERE ("examples_book"."data" #> '{specs,format}') = '"hardcover"'::jsonb

# Result:
#   Harry Potter
#   The Lord of the Rings

# ----------------------------------------------------------------------
# data.specs.stock = 0
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(data__specs__stock=0)

# 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"
#   FROM "examples_book"
#   WHERE ("examples_book"."data" #> '{specs,stock}') = '0'::jsonb

# Result:
#   The Lord of the Rings

# ----------------------------------------------------------------------
# data contains {'specs': {'format': 'ebook'}}
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(data__contains={"specs": {"format": "ebook"}})

# 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"
#   FROM "examples_book"
#   WHERE "examples_book"."data" @> '{"specs": {"format": "ebook"}}'::jsonb

# Result:
#   Postgres for Authors

# ----------------------------------------------------------------------
# data has_key 'isbn'
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(data__has_key="isbn")

# 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"
#   FROM "examples_book"
#   WHERE "examples_book"."data" ? 'isbn'

# Result:
#   Harry Potter
#   The Hobbit
#   The Lord of the Rings
#   Postgres for Authors
May 22, 2026 python
Django ORM PostgreSQL

ArrayField lookups (contains, overlap, len, index)

ArrayField maps to a real PostgreSQL array.

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
59
60
61
62
63
64
65
66
67
68
69
70
71
# ======================================================================
# `ArrayField` maps to a real PostgreSQL array. The lookups become array
# operators: `__contains` → `@>`, `__overlap` → `&&`, `__len` → `array_length`,
# and `__0` indexes the first element. A GIN index makes containment/overlap
# fast.
# ======================================================================

# ----------------------------------------------------------------------
# tags contains ['fantasy']
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(tags__contains=["fantasy"])

# 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"
#   FROM "examples_book"
#   WHERE "examples_book"."tags" @> (ARRAY['fantasy'])::varchar(30)[]

# Result:
#   Harry Potter
#   The Hobbit
#   The Lord of the Rings

# ----------------------------------------------------------------------
# tags overlap ['tech','epic']
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(tags__overlap=["tech", "epic"])

# 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"
#   FROM "examples_book"
#   WHERE "examples_book"."tags" && (ARRAY['tech', 'epic'])::varchar(30)[]

# Result:
#   The Lord of the Rings
#   Postgres for Authors

# ----------------------------------------------------------------------
# exactly 3 tags (tags__len=3)
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(tags__len=3)

# 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"
#   FROM "examples_book"
#   WHERE CASE WHEN "examples_book"."tags" IS NULL THEN NULL ELSE coalesce(array_length("examples_book"."tags", 1), 0) END = 3

# Result:
#   The Lord of the Rings

# ----------------------------------------------------------------------
# first tag = 'fantasy' (tags__0)
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(tags__0="fantasy")

# 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"
#   FROM "examples_book"
#   WHERE ("examples_book"."tags")[1] = 'fantasy'

# Result:
#   Harry Potter
#   The Hobbit
#   The Lord of the Rings
May 22, 2026 python
Django ORM PostgreSQL

Subquery / OuterRef / Exists

OuterRef('pk') refers to the outer row from inside a subquery.

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
# ======================================================================
# `OuterRef('pk')` refers to the outer row from inside a subquery.
# `Subquery(qs[:1])` pulls a single correlated value per row, while `Exists()`
# compiles to SQL EXISTS — far cheaper than counting when you only need a
# yes/no.
# ======================================================================

# ----------------------------------------------------------------------
# per author
# ----------------------------------------------------------------------

# Django:
newest = Book.objects.filter(author=OuterRef("pk")).order_by("-published")
Author.objects.annotate(
    newest_title=Subquery(newest.values("title")[:1]),
    has_free=Exists(Book.objects.filter(author=OuterRef("pk"), price=0)),
)

# SQL:
#   SELECT "examples_author"."id", "examples_author"."name", "examples_author"."bio", "examples_author"."born", "examples_author"."rating", "examples_author"."nickname", (SELECT U0."title" AS "title"
#   FROM "examples_book" U0
#   WHERE U0."author_id" = ("examples_author"."id")
#   ORDER BY U0."published" DESC
#   LIMIT 1) AS "newest_title", EXISTS(SELECT 1 AS "a"
#   FROM "examples_book" U0
#   WHERE (U0."author_id" = ("examples_author"."id") AND U0."price" = 0)
#   LIMIT 1) AS "has_free"
#   FROM "examples_author"

# Result:
#   J. K. Rowling: newest='Harry Potter'  has_free_book=False
#   J. R. R. Tolkien: newest='The Lord of the Rings'  has_free_book=False
#   Karl Müller: newest='Postgres for Authors'  has_free_book=True
May 22, 2026 python
Django ORM PostgreSQL

Case/When, F arithmetic, ExpressionWrapper, Coalesce

Case/When is SQL CASE.

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
# ======================================================================
# `Case`/`When` is SQL CASE. `F()` references a column so the arithmetic happens
# in the database, not Python. `ExpressionWrapper` declares the output type for
# mixed-type math (Decimal × Integer). `Coalesce` returns the first non-NULL
# argument.
# ======================================================================

# ----------------------------------------------------------------------
# price tier + price*pages
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(
    tier=Case(
        When(price__gte=25, then=Value("premium")),
        When(price__gte=10, then=Value("standard")),
        default=Value("free"),
    ),
    value=ExpressionWrapper(
        F("price") * F("pages"),
        output_field=DecimalField(max_digits=12, decimal_places=2),
    ),
).values_list("title", "tier", "value")

# SQL:
#   SELECT "examples_book"."title" AS "title", CASE WHEN "examples_book"."price" >= 25 THEN 'premium' WHEN "examples_book"."price" >= 10 THEN 'standard' ELSE 'free' END AS "tier", ("examples_book"."price" * "examples_book"."pages") AS "value"
#   FROM "examples_book"

# Result:
#   Harry Potter: standard  (price*pages=6396.80)
#   The Hobbit: standard  (price*pages=4495.00)
#   The Lord of the Rings: premium  (price*pages=34162.00)
#   Postgres for Authors: free  (price*pages=0.00)

# ----------------------------------------------------------------------
# Coalesce(bio, '(no bio)')
# ----------------------------------------------------------------------

# Django:
Author.objects.annotate(
    b=Coalesce("bio", Value("(no bio)"), output_field=TextField())
).values_list("name", "b")

# SQL:
#   SELECT "examples_author"."name" AS "name", COALESCE("examples_author"."bio", '(no bio)') AS "b"
#   FROM "examples_author"

# Result:
#   J. K. Rowling: British author
#   J. R. R. Tolkien: (no bio)
#   Karl Müller: Schriftsteller
May 22, 2026 python