Django ORM PostgreSQL

Django ORM PostgreSQL

Explore django orm postgresql code snippets and tutorials

Django ORM PostgreSQL

Bulk upsert (INSERT ... ON CONFLICT DO UPDATE)

bulk_create(update_conflicts=True, unique_fields=[...], update_fields=[...]) compiles to INSERT ...

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
# ======================================================================
# `bulk_create(update_conflicts=True, unique_fields=[...], update_fields=[...])`
# compiles to `INSERT ... ON CONFLICT DO UPDATE`: insert the rows, but when a
# row with the same unique key already exists, update just the listed fields
# instead of erroring.
# ======================================================================

# ----------------------------------------------------------------------
# The Hobbit price
# ----------------------------------------------------------------------

# Django:
Book.objects.bulk_create(
    [Book(id=hobbit.id, title="The Hobbit", author=hobbit.author,
          price="99.99", pages=hobbit.pages, published=hobbit.published)],
    update_conflicts=True,
    unique_fields=["id"],
    update_fields=["price"],
)

# 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"."id" = 47
#   LIMIT 21
#
#   BEGIN
#
#   INSERT INTO "examples_book" ("id", "title", "author_id", "price", "pages", "published", "is_active", "tags", "data", "search")
#   VALUES (62, 'The Hobbit', 47, 99.99, 310, '1937-09-21 00:00:00+00:00'::timestamptz, true, '{}'::varchar(30)[], '{}'::jsonb, NULL)
#   ON CONFLICT("id")
#   DO UPDATE SET "price" = EXCLUDED."price"
#   RETURNING "examples_book"."id", "examples_book"."title_upper"
#
#   COMMIT

# Result:
#   before: $14.50
#   after upsert: $99.99
#   (restored)
May 22, 2026 python
Django ORM PostgreSQL

Migration operations (code only — run at migrate time)

These run during migrate, not in queries, so there's no result row — only the Django code and the SQL it emits.

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
# ======================================================================
# These run during `migrate`, not in queries, so there's no result row — only
# the Django code and the SQL it emits. They cover zero-downtime schema changes:
# concurrent index builds, validating constraints in two steps, managing
# collations, and enabling extensions.
# ======================================================================

# ----------------------------------------------------------------------
# AddIndexConcurrently / RemoveIndexConcurrently
# ----------------------------------------------------------------------

# Django:
from django.contrib.postgres.operations import AddIndexConcurrently
from django.contrib.postgres.indexes import GinIndex

class Migration(migrations.Migration):
    atomic = False
    operations = [
        AddIndexConcurrently(
            "book", GinIndex(fields=["tags"], name="book_tags_gin2")),
    ]

# SQL:
#   CREATE INDEX CONCURRENTLY "book_tags_gin2" ON "examples_book" USING gin ("tags");

# Result:
#   Builds (or drops) an index WITHOUT locking the table for writes.
#   Must run in a non-atomic migration (`atomic = False`).

# ----------------------------------------------------------------------
# AddConstraintNotValid + ValidateConstraint
# ----------------------------------------------------------------------

# Django:
from django.contrib.postgres.operations import (
    AddConstraintNotValid, ValidateConstraint)
from django.db.models import CheckConstraint, Q

operations = [
    AddConstraintNotValid(
        "book", CheckConstraint(condition=Q(price__gte=0), name="price_ok")),
    ValidateConstraint("book", "price_ok"),
]

# SQL:
#   ALTER TABLE "examples_book" ADD CONSTRAINT "price_ok" CHECK ("price" >= 0) NOT VALID;
#
#   ALTER TABLE "examples_book" VALIDATE CONSTRAINT "price_ok";

# Result:
#   Add a CHECK constraint without scanning existing rows (instant),
#   then validate it separately — avoids a long write lock on big tables.

# ----------------------------------------------------------------------
# CreateCollation / RemoveCollation
# ----------------------------------------------------------------------

# Django:
from django.contrib.postgres.operations import CreateCollation

operations = [
    CreateCollation("case_insensitive", provider="icu",
                    locale="und-u-ks-level2", deterministic=False),
]

# SQL:
#   CREATE COLLATION "case_insensitive" (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

# Result:
#   Manage database collations from migrations.
#   This project uses CreateCollation('case_insensitive', …) in migration 0001.

# ----------------------------------------------------------------------
# Extension operations (BloomExtension, CreateExtension, …)
# ----------------------------------------------------------------------

# Django:
from django.contrib.postgres.operations import (
    BloomExtension, CreateExtension)

operations = [BloomExtension(), CreateExtension("cube")]

# SQL:
#   CREATE EXTENSION IF NOT EXISTS "bloom";
#
#   CREATE EXTENSION IF NOT EXISTS "cube";

# Result:
#   Enable a contrib module / extension from a migration.
#   Already used here: HStore, Trigram, BtreeGist, Unaccent, Crypto.
#   BloomExtension enables space-efficient bloom indexes.
May 22, 2026 python
Django ORM PostgreSQL

Concurrency & EXPLAIN

select_for_update() issues SELECT ...

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
# ======================================================================
# `select_for_update()` issues `SELECT ... FOR UPDATE` to lock rows for the
# transaction; `skip_locked=True` skips rows another transaction already holds
# (a simple work-queue pattern). `QuerySet.explain()` returns PostgreSQL's query
# plan — how it will execute.
# ======================================================================

# ----------------------------------------------------------------------
# select_for_update(skip_locked=True) — row locking
# ----------------------------------------------------------------------

# Django:
with transaction.atomic():
    rows = list(
        Book.objects.select_for_update(skip_locked=True).order_by("id")[:3]
    )

# SQL:
#   BEGIN
#
#   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"
#   ORDER BY "examples_book"."id" ASC
#   LIMIT 3 FOR UPDATE SKIP LOCKED
#
#   COMMIT

# Result:
#   locked & fetched: Harry Potter
#   locked & fetched: The Hobbit
#   locked & fetched: The Lord of the Rings

# ----------------------------------------------------------------------
# QuerySet.explain() — PostgreSQL's query plan
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(price__gt=10).explain()

# Result:
#   Seq Scan on examples_book  (cost=0.00..1.05 rows=3 width=222)
#     Filter: (price > '10'::numeric)
May 22, 2026 python
Django ORM PostgreSQL

Indexes (GIN, GiST, SP-GiST, BRIN, B-tree, Hash, partial, covering)

Indexes live in a model's Meta.indexes; migrations emit the CREATE INDEX DDL shown below (read live from pg_indexes).

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
# ======================================================================
# Indexes live in a model's `Meta.indexes`; migrations emit the `CREATE INDEX`
# DDL shown below (read live from `pg_indexes`). PostgreSQL has many index
# types: GIN for arrays/jsonb/full-text, GiST & SP-GiST for ranges, BRIN for
# huge time-ordered tables, B-tree for ordering, Hash for equality, plus partial
# (`condition=`) and covering (`include=`) indexes, and per-type options
# (`fastupdate`, `pages_per_range`, `fillfactor`).
# ======================================================================

# ----------------------------------------------------------------------
# indexes across the three tables (live from pg_indexes)
# ----------------------------------------------------------------------

# Django:
class Book(models.Model):
    class Meta:
        indexes = [
            GinIndex(fields=["tags"], fastupdate=False),          # arrays / jsonb (+option)
            GinIndex(fields=["search"]),                          # full-text
            GinIndex(OpClass(F("title"), name="gin_trgm_ops")),   # trigram
            BrinIndex(fields=["published"], pages_per_range=64),  # time-ordered (+option)
            BTreeIndex(fields=["pages"], fillfactor=90),          # ordering (+option)
            Index(fields=["price"], condition=Q(is_active=True)), # partial
            Index(fields=["author"], include=["title", "price"]), # covering
        ]

class Reservation(models.Model):
    class Meta:
        indexes = [
            GistIndex(fields=["during"]),                         # GiST for ranges
            SpGistIndex(fields=["during"]),                       # SP-GiST for ranges
        ]

class Server(models.Model):
    class Meta:
        indexes = [HashIndex(fields=["hostname"])]                # hash index

# SQL:
#   CREATE INDEX book_active_price ON public.examples_book USING btree (price) WHERE is_active;
#
#   CREATE INDEX book_author_cover ON public.examples_book USING btree (author_id) INCLUDE (title, price);
#
#   CREATE INDEX book_pages_btree ON public.examples_book USING btree (pages) WITH (fillfactor='90');
#
#   CREATE INDEX book_pub_brin ON public.examples_book USING brin (published) WITH (pages_per_range='64');
#
#   CREATE INDEX book_search_gin ON public.examples_book USING gin (search);
#
#   CREATE INDEX book_tags_gin ON public.examples_book USING gin (tags) WITH (fastupdate=off);
#
#   CREATE INDEX book_title_trgm ON public.examples_book USING gin (title gin_trgm_ops);
#
#   CREATE INDEX examples_book_author_id_ab830087 ON public.examples_book USING btree (author_id);
#
#   CREATE UNIQUE INDEX examples_book_pkey ON public.examples_book USING btree (id);
#
#   CREATE UNIQUE INDEX uq_book_title_author_ci ON public.examples_book USING btree (lower((title)::text), author_id);
#
#   CREATE UNIQUE INDEX examples_reservation_pkey ON public.examples_reservation USING btree (id);
#
#   CREATE INDEX exclude_overlapping_reservations ON public.examples_reservation USING gist (room, during);
#
#   CREATE INDEX resv_during_gist ON public.examples_reservation USING gist (during);
#
#   CREATE INDEX resv_during_spgist ON public.examples_reservation USING spgist (during);
#
#   CREATE UNIQUE INDEX examples_server_pkey ON public.examples_server USING btree (id);
#
#   CREATE INDEX server_host_hash ON public.examples_server USING hash (hostname)

# Result:
#   book_active_price
#   book_author_cover
#   book_pages_btree
#   book_pub_brin
#   book_search_gin
#   book_tags_gin
#   book_title_trgm
#   examples_book_author_id_ab830087
#   examples_book_pkey
#   uq_book_title_author_ci
#   examples_reservation_pkey
#   exclude_overlapping_reservations
#   resv_during_gist
#   resv_during_spgist
#   examples_server_pkey
#   server_host_hash
May 22, 2026 python
Django ORM PostgreSQL

More lookups (contained_by, range bounds, has_keys, trigram word)

The remaining lookup variants: __contained_by (arrays/json/ranges — the inverse of __contains), __fully_gt, range bound transforms like __lower_inc, JSON __has_keys/__has_any_keys, and __trigram_word_similar (a fuzzy word match inside a longer string).

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
121
122
123
124
125
126
# ======================================================================
# The remaining lookup variants: `__contained_by` (arrays/json/ranges — the
# inverse of `__contains`), `__fully_gt`, range bound transforms like
# `__lower_inc`, JSON `__has_keys`/`__has_any_keys`, and
# `__trigram_word_similar` (a fuzzy word match inside a longer string).
# ======================================================================

# ----------------------------------------------------------------------
# tags contained_by a superset
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(tags__contained_by=["fantasy","magic","epic","adventure","tech","database"])

# 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', 'magic', 'epic', 'adventure', 'tech', 'database'])::varchar(30)[]

# Result:
#   Harry Potter
#   The Hobbit
#   The Lord of the Rings
#   Postgres for Authors

# ----------------------------------------------------------------------
# data has_keys ['isbn', 'specs']
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(data__has_keys=["isbn", "specs"])

# 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,specs}'

# Result:
#   Harry Potter
#   The Hobbit
#   The Lord of the Rings
#   Postgres for Authors

# ----------------------------------------------------------------------
# data has_any_keys ['isbn', 'missing']
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(data__has_any_keys=["isbn", "missing"])

# 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,missing}'

# Result:
#   Harry Potter
#   The Hobbit
#   The Lord of the Rings
#   Postgres for Authors

# ----------------------------------------------------------------------
# price_band fully above [0, 50) (__fully_gt)
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(price_band__fully_gt=Range(0, 50))

# 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" >> '[0,50)'

# 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 contained_by [6/1, 6/30) (__contained_by)
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(during__contained_by=Range(dt(2026,6,1), dt(2026,6,30)))

# 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-01 00:00:00+00:00","2026-06-30 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)

# ----------------------------------------------------------------------
# price_band lower bound inclusive (__lower_inc=True)
# ----------------------------------------------------------------------

# Django:
Reservation.objects.filter(price_band__lower_inc=True)

# 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_INC("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)

# ----------------------------------------------------------------------
# title trigram-word-similar to 'lord' (__trigram_word_similar)
# ----------------------------------------------------------------------

# Django:
Book.objects.filter(title__trigram_word_similar="lord")

# 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"."title" %> 'lord'

# Result:
#   The Lord of the Rings
May 22, 2026 python