Django ORM PostgreSQL

Django ORM PostgreSQL

Explore django orm postgresql code snippets and tutorials

Django ORM PostgreSQL

Range fields — the full lookups

Range fields (DateTimeRange, DateRange, IntegerRange, DecimalRange) store an interval in one column.

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
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
# ======================================================================
# Range fields (`DateTimeRange`, `DateRange`, `IntegerRange`, `DecimalRange`)
# store an interval in one column. Lookups map to PostgreSQL range operators:
# `__overlap` (`&&`), `__adjacent_to` (`-|-`), `__fully_lt`/`__fully_gt`
# (`<<`/`>>`), `__contains` (`@>`), `__contained_by` (`<@`), plus bound
# transforms like `__startswith` (lower bound) and `__isempty`.
# ======================================================================

# ----------------------------------------------------------------------
# during overlaps [6/4, 6/7)
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(during__overlap=Range(dt(2026,6,4), dt(2026,6,7)))

# SQL:
#   SELECT "examples_reservation"."id", "examples_reservation"."room", "examples_reservation"."during", "examples_reservation"."price_band", "examples_reservation"."date_span", "examples_reservation"."amount", "examples_reservation"."seq"
#   FROM "examples_reservation"
#   WHERE "examples_reservation"."during" && '["2026-06-04 00:00:00+00:00","2026-06-07 00:00:00+00:00")'::tstzrange

# Result:
#   room 101 [2026-06-01 00:00:00+00:00, 2026-06-05 00:00:00+00:00)
#   room 101 [2026-06-05 00:00:00+00:00, 2026-06-09 00:00:00+00:00)
#   room 202 [2026-06-02 00:00:00+00:00, 2026-06-06 00:00:00+00:00)

# ----------------------------------------------------------------------
# during adjacent to [6/9, 6/12)
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(during__adjacent_to=Range(dt(2026,6,9), dt(2026,6,12)))

# SQL:
#   SELECT "examples_reservation"."id", "examples_reservation"."room", "examples_reservation"."during", "examples_reservation"."price_band", "examples_reservation"."date_span", "examples_reservation"."amount", "examples_reservation"."seq"
#   FROM "examples_reservation"
#   WHERE "examples_reservation"."during" -|- '["2026-06-09 00:00:00+00:00","2026-06-12 00:00:00+00:00")'::tstzrange

# Result:
#   room 101 [2026-06-05 00:00:00+00:00, 2026-06-09 00:00:00+00:00)

# ----------------------------------------------------------------------
# price_band fully below [300, 400)
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(price_band__fully_lt=Range(300, 400))

# SQL:
#   SELECT "examples_reservation"."id", "examples_reservation"."room", "examples_reservation"."during", "examples_reservation"."price_band", "examples_reservation"."date_span", "examples_reservation"."amount", "examples_reservation"."seq"
#   FROM "examples_reservation"
#   WHERE "examples_reservation"."price_band" << '[300,400)'

# Result:
#   room 101 [2026-06-01 00:00:00+00:00, 2026-06-05 00:00:00+00:00)
#   room 101 [2026-06-05 00:00:00+00:00, 2026-06-09 00:00:00+00:00)
#   room 202 [2026-06-02 00:00:00+00:00, 2026-06-06 00:00:00+00:00)

# ----------------------------------------------------------------------
# price_band contains the value 175
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(price_band__contains=175)

# SQL:
#   SELECT "examples_reservation"."id", "examples_reservation"."room", "examples_reservation"."during", "examples_reservation"."price_band", "examples_reservation"."date_span", "examples_reservation"."amount", "examples_reservation"."seq"
#   FROM "examples_reservation"
#   WHERE "examples_reservation"."price_band" @> (175)::integer

# Result:
#   room 101 [2026-06-01 00:00:00+00:00, 2026-06-05 00:00:00+00:00)
#   room 101 [2026-06-05 00:00:00+00:00, 2026-06-09 00:00:00+00:00)

# ----------------------------------------------------------------------
# amount range overlaps [190, 210)
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(amount__overlap=Range(Decimal("190"), Decimal("210")))

# SQL:
#   SELECT "examples_reservation"."id", "examples_reservation"."room", "examples_reservation"."during", "examples_reservation"."price_band", "examples_reservation"."date_span", "examples_reservation"."amount", "examples_reservation"."seq"
#   FROM "examples_reservation"
#   WHERE "examples_reservation"."amount" && '[190,210)'::numrange

# Result:
#   room 101 [2026-06-01 00:00:00+00:00, 2026-06-05 00:00:00+00:00)
#   room 101 [2026-06-05 00:00:00+00:00, 2026-06-09 00:00:00+00:00)

# ----------------------------------------------------------------------
# date_span lower bound is 2026-06-01 (__startswith)
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(date_span__startswith=date(2026, 6, 1))

# SQL:
#   SELECT "examples_reservation"."id", "examples_reservation"."room", "examples_reservation"."during", "examples_reservation"."price_band", "examples_reservation"."date_span", "examples_reservation"."amount", "examples_reservation"."seq"
#   FROM "examples_reservation"
#   WHERE lower("examples_reservation"."date_span") = '2026-06-01'::date

# Result:
#   room 101 [2026-06-01 00:00:00+00:00, 2026-06-05 00:00:00+00:00)

# ----------------------------------------------------------------------
# price_band is not empty (__isempty=False)
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(price_band__isempty=False)

# SQL:
#   SELECT "examples_reservation"."id", "examples_reservation"."room", "examples_reservation"."during", "examples_reservation"."price_band", "examples_reservation"."date_span", "examples_reservation"."amount", "examples_reservation"."seq"
#   FROM "examples_reservation"
#   WHERE NOT isempty("examples_reservation"."price_band")

# Result:
#   room 101 [2026-06-01 00:00:00+00:00, 2026-06-05 00:00:00+00:00)
#   room 101 [2026-06-05 00:00:00+00:00, 2026-06-09 00:00:00+00:00)
#   room 202 [2026-06-02 00:00:00+00:00, 2026-06-06 00:00:00+00:00)
May 22, 2026 python
Django ORM PostgreSQL

Range fields + EXCLUDE constraint

Range fields store an interval (e.g.

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
# ======================================================================
# Range fields store an interval (e.g. a booking's start/end) in a single
# column. `__contains` tests membership. The `ExclusionConstraint` uses the `&&`
# overlap operator to forbid two reservations for the same room from overlapping
# — enforced by the database.
# ======================================================================

# ----------------------------------------------------------------------
# reservations covering 2026-06-03
# ----------------------------------------------------------------------

# Django:
probe = datetime(2026, 6, 3, tzinfo=timezone.utc)
Reservation.objects.filter(during__contains=probe)

# SQL:
#   SELECT "examples_reservation"."id", "examples_reservation"."room", "examples_reservation"."during", "examples_reservation"."price_band", "examples_reservation"."date_span", "examples_reservation"."amount", "examples_reservation"."seq"
#   FROM "examples_reservation"
#   WHERE "examples_reservation"."during" @> '2026-06-03 00:00:00+00:00'::timestamptz::timestamp with time zone

# Result:
#   room 101 [2026-06-01 00:00:00+00:00, 2026-06-05 00:00:00+00:00)
#   room 202 [2026-06-02 00:00:00+00:00, 2026-06-06 00:00:00+00:00)

# ----------------------------------------------------------------------
# insert OVERLAPPING reservation for room 101
# ----------------------------------------------------------------------

# Django:
# blocked by the EXCLUDE constraint (room = ... && during overlaps)
Reservation.objects.create(
    room=101,
    during=Range(datetime(2026, 6, 3, tzinfo=timezone.utc),
                 datetime(2026, 6, 7, tzinfo=timezone.utc)),
)

# SQL:
#   BEGIN
#
#   INSERT INTO "examples_reservation" ("room", "during", "price_band", "date_span", "amount", "seq")
#   VALUES (101, '["2026-06-03 00:00:00+00:00","2026-06-07 00:00:00+00:00")'::tstzrange::tstzrange, NULL::int4range, NULL::daterange, NULL::numrange, NULL::int8range)
#   RETURNING "examples_reservation"."id"
#
#   ROLLBACK

# Result:
#   correctly rejected: conflicting key value violates exclusion constraint "exclude_overlapping_reservations"
May 22, 2026 python
Django ORM PostgreSQL

Search extras (Headline, weighted rank, trigram distance/word)

Beyond basic search: SearchHeadline returns a snippet with matches wrapped in markers; weighting a SearchVector (A > B > C > D) influences SearchRank; TrigramDistance is 1 − similarity (order …

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
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
# ======================================================================
# Beyond basic search: `SearchHeadline` returns a snippet with matches wrapped
# in markers; weighting a `SearchVector` (A > B > C > D) influences
# `SearchRank`; `TrigramDistance` is `1 − similarity` (order ascending for
# closest); and `TrigramWordSimilarity` matches a word *inside* a longer string.
# ======================================================================

# ----------------------------------------------------------------------
# SearchHeadline wraps matches in [ ]
# ----------------------------------------------------------------------

# Django:
query = SearchQuery("rings", search_type="websearch")
Book.objects.annotate(
    headline=SearchHeadline("title", query, start_sel="[", stop_sel="]")
).values_list("title", "headline")

# SQL:
#   SELECT "examples_book"."title" AS "title", ts_headline("examples_book"."title", websearch_to_tsquery('rings'), 'StartSel=''['', StopSel='']''') AS "headline"
#   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

# ----------------------------------------------------------------------
# weighted SearchVector (A) + SearchRank
# ----------------------------------------------------------------------

# Django:
vector = SearchVector("title", weight="A")
query = SearchQuery("postgres")
Book.objects.annotate(rank=SearchRank(vector, query)) \
    .filter(rank__gt=0).order_by("-rank").values_list("title", "rank")

# SQL:
#   SELECT "examples_book"."title" AS "title", ts_rank(setweight(to_tsvector(COALESCE("examples_book"."title", '')), 'A'), plainto_tsquery('postgres')) AS "rank"
#   FROM "examples_book"
#   WHERE ts_rank(setweight(to_tsvector(COALESCE("examples_book"."title", '')), 'A'), plainto_tsquery('postgres')) > 0.0
#   ORDER BY 2 DESC

# Result:
#   Postgres for Authors: 0.6079

# ----------------------------------------------------------------------
# TrigramDistance (ascending = closest match)
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(dist=TrigramDistance("title", "potter")) \
    .order_by("dist").values_list("title", "dist")[:3]

# SQL:
#   SELECT "examples_book"."title" AS "title", ("examples_book"."title" <-> 'potter') AS "dist"
#   FROM "examples_book"
#   ORDER BY 2 ASC
#   LIMIT 3

# Result:
#   Harry Potter: 0.462
#   Postgres for Authors: 0.923
#   The Hobbit: 1.000

# ----------------------------------------------------------------------
# TrigramWordSimilarity ('lord' as a word inside the title)
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(sim=TrigramWordSimilarity("lord", "title")) \
    .order_by("-sim").values_list("title", "sim")[:3]

# SQL:
#   SELECT "examples_book"."title" AS "title", WORD_SIMILARITY('lord', "examples_book"."title") AS "sim"
#   FROM "examples_book"
#   ORDER BY 2 DESC
#   LIMIT 3

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

# ----------------------------------------------------------------------
# TrigramWordDistance + Strict word similarity/distance
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(
    wd=TrigramWordDistance("lord", "title"),
    ss=TrigramStrictWordSimilarity("lord", "title"),
    sd2=TrigramStrictWordDistance("lord", "title"),
).order_by("wd").values_list("title", "wd", "ss", "sd2")[:3]

# SQL:
#   SELECT "examples_book"."title" AS "title", ('lord' <<-> "examples_book"."title") AS "wd", STRICT_WORD_SIMILARITY('lord', "examples_book"."title") AS "ss", ('lord' <<<-> "examples_book"."title") AS "sd2"
#   FROM "examples_book"
#   ORDER BY 2 ASC
#   LIMIT 3

# Result:
#   The Lord of the Rings: word_dist=0.000 strict_sim=1.000 strict_dist=0.000
#   Harry Potter: word_dist=1.000 strict_sim=0.000 strict_dist=1.000
#   The Hobbit: word_dist=1.000 strict_sim=0.000 strict_dist=1.000
May 22, 2026 python
Django ORM PostgreSQL

Full-text search & trigram similarity

SearchVector/SearchQuery/SearchRank are PostgreSQL full-text search; websearch parses Google-style queries (OR, quotes, -).

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
# ======================================================================
# `SearchVector`/`SearchQuery`/`SearchRank` are PostgreSQL full-text search;
# `websearch` parses Google-style queries (`OR`, quotes, `-`).
# `TrigramSimilarity` (pg_trgm) is typo-tolerant fuzzy matching, and
# `__unaccent` strips diacritics so 'muller' finds 'Müller'.
# ======================================================================

# ----------------------------------------------------------------------
# websearch 'lord OR postgres' ranked
# ----------------------------------------------------------------------

# Django:
query = SearchQuery("lord OR postgres", search_type="websearch")
Book.objects.annotate(rank=SearchRank("search", query)) \
    .filter(search=query).order_by("-rank") \
    .values_list("title", "rank")

# SQL:
#   SELECT "examples_book"."title" AS "title", ts_rank(to_tsvector(COALESCE(("examples_book"."search")::text, '')), websearch_to_tsquery('lord OR postgres')) AS "rank"
#   FROM "examples_book"
#   WHERE "examples_book"."search" @@ (websearch_to_tsquery('lord OR postgres'))
#   ORDER BY 2 DESC

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

# ----------------------------------------------------------------------
# trigram similarity to 'potter'
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(sim=TrigramSimilarity("title", "potter")) \
    .filter(sim__gt=0.2).order_by("-sim") \
    .values_list("title", "sim")

# SQL:
#   SELECT "examples_book"."title" AS "title", SIMILARITY("examples_book"."title", 'potter') AS "sim"
#   FROM "examples_book"
#   WHERE SIMILARITY("examples_book"."title", 'potter') > 0.2
#   ORDER BY 2 DESC

# Result:
#   Harry Potter: 0.538

# ----------------------------------------------------------------------
# __unaccent: 'muller' matches 'Müller'
# ----------------------------------------------------------------------

# Django:
Author.objects.filter(name__unaccent__icontains="muller")

# SQL:
#   SELECT "examples_author"."id", "examples_author"."name", "examples_author"."bio", "examples_author"."born", "examples_author"."rating", "examples_author"."nickname"
#   FROM "examples_author"
#   WHERE UPPER(UNACCENT("examples_author"."name")::text) LIKE '%' || UPPER(REPLACE(REPLACE(REPLACE((UNACCENT('muller')), E'\\', E'\\\\'), E'%', E'\\%'), E'_', E'\\_')) || '%'

# Result:
#   Karl Müller
May 22, 2026 python
Django ORM PostgreSQL

HStoreField lookups

HStoreField is a flat string→string map.

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
# ======================================================================
# `HStoreField` is a flat string→string map. Query a key directly
# (`labels__env`), check a key exists (`__has_key`), or test containment
# (`__contains`). Requires the `hstore` extension (enabled in migration 0001).
# ======================================================================

# ----------------------------------------------------------------------
# labels.env = 'prod'
# ----------------------------------------------------------------------

# Django:
Server.objects.filter(labels__env="prod")

# SQL:
#   SELECT "examples_server"."id", "examples_server"."hostname", "examples_server"."labels"
#   FROM "examples_server"
#   WHERE ("examples_server"."labels" -> 'env') = 'prod'

# Result:
#   web-1

# ----------------------------------------------------------------------
# labels has_key 'region'
# ----------------------------------------------------------------------

# Django:
Server.objects.filter(labels__has_key="region")

# SQL:
#   SELECT "examples_server"."id", "examples_server"."hostname", "examples_server"."labels"
#   FROM "examples_server"
#   WHERE "examples_server"."labels" ? 'region'

# Result:
#   web-1
#   web-2

# ----------------------------------------------------------------------
# labels contains {'env': 'staging'}
# ----------------------------------------------------------------------

# Django:
Server.objects.filter(labels__contains={"env": "staging"})

# SQL:
#   SELECT "examples_server"."id", "examples_server"."hostname", "examples_server"."labels"
#   FROM "examples_server"
#   WHERE "examples_server"."labels" @> '"env"=>"staging"'::hstore

# Result:
#   web-2
May 22, 2026 python