Query Expressions

F()/Q(), Case/When conditional logic, and database functions — computing inside the database instead of after fetching

F() avoids read-then-write racesrefresh_from_db() to see the real value afterward
Q() for OR/NOTfilter() kwargs alone can only ever express AND
Case/When = SQL CASE in Pythonalways set default= or unmatched rows get NULL
Cast converts; ExpressionWrapper doesn'tExpressionWrapper only sets the Python-side output_field hint

1.F() and Q() expressions

F("field_name") refers to a field's value inside the database itself, without pulling it into Python — used for race-safe increments (F("count") + 1) and for comparing two fields on the same row. Q(...) wraps a lookup so multiple lookups can be combined with & (AND), | (OR), and ~ (NOT) — needed the moment a filter needs OR logic or NOT logic, which filter()'s own keyword arguments cannot express.

Key features

F("field") + 1 as an assignment defers the actual addition to the database — reporter.count = F("count") + 1; reporter.save() is race-safe, unlike reporter.count += 1
After an F()-based save(), the in-memory attribute is NOT the new value — refresh_from_db() (or re-fetching) is required to see the actual updated number
An F() assignment on an instance PERSISTS — a second .save() call re-applies the same +1 again unless the field is reset first
Q(a=1) | Q(b=2) is OR; Q(a=1) & Q(b=2) is AND (same as filter(a=1, b=2)); ~Q(a=1) is NOT
F() can also compare two fields directly in a filter: filter(end_date__lt=F("start_date")) — a comparison the database evaluates per-row

F() vs Q() — what each solves

F() vs Q() — what each solves
Need	Tool	Why filter() kwargs alone can't
Increment a field atomically	F("count") + 1	a plain += reads then writes — two steps, a race window
Compare two fields on the same row	filter(a__gt=F("b"))	kwargs compare a field to a Python value, not to another field
OR two conditions	Q(a=1) \| Q(b=2)	filter(a=1, b=2) is always AND
NOT a condition	~Q(a=1)	exclude(a=1) works for one condition, not for combining with OR

F() moves computation into SQL; Q() adds OR/NOT to filter()

F("count") + 1

Computed inside the database, atomically
Race-safe — no read-then-write window
refresh_from_db() needed to see the real value

Q(a=1) | Q(b=2)

filter() kwargs are always AND
Q wraps a lookup for &, |, ~
The only way to express OR/NOT

Command

python

Model.objects.update(count=F("count") + 1)
Model.objects.filter(Q(a=1) | Q(b=2))

F("field") defers a computation to the database (race-safe increments, cross-field comparisons) — refresh_from_db() is required to see the real value in Python afterward. Q(...) wraps a lookup so filter() can express OR/NOT, which plain keyword arguments never can.

2.Value(), Case/When, and ExpressionWrapper

Value(x) wraps a literal so it can participate in a database expression (Django usually infers this automatically, but explicit wrapping matters when the type can't be inferred). Case(When(condition, then=x), ..., default=y) is SQL CASE/WHEN as a Python expression — an if/elif/else computed inside the database. ExpressionWrapper wraps an expression to declare its output_field explicitly, needed whenever Django can't infer the combined result type on its own (e.g. adding a DateTimeField and a DurationField).

Key features

Case(When(condition=..., then=value), ..., default=value, output_field=...) is SQL CASE/WHEN — evaluated per-row, inside the database
When's condition is usually a Q object or a lookup expression; then= is the resulting value for matching rows
default= is required to cover any row matching none of the When clauses — otherwise those rows get NULL
ExpressionWrapper(expression, output_field=SomeField()) declares what TYPE Django should treat a combined expression as — required specifically when combining fields of genuinely different types
ExpressionWrapper does NOT cast anything at the database level — it only tells the ORM how to interpret the result in Python; Cast() is the tool for an actual database-level type conversion

When each tool is needed

When each tool is needed
Situation	Tool
A literal needs to participate in an expression Django can't auto-wrap	Value(x)
A column's value should depend on a condition, computed in the database	Case(When(...), default=...)
Combining two different field types and Django can't infer the result type	ExpressionWrapper(..., output_field=...)
An actual database-level type conversion (not just a Python-side type hint)	Cast(..., output_field=...)

Command

python

Case(When(condition, then=Value(x)), default=Value(y), output_field=SomeField())
ExpressionWrapper(F("a") + F("b"), output_field=SomeField())

Case/When moves conditional branching into the database — always include default= or unmatched rows get NULL. ExpressionWrapper declares an expression's output_field when combining different field types makes the result type ambiguous; it does not itself perform a database-level type conversion (that's Cast()).

3.Database functions: Cast, Coalesce, and the function families

Database functions run SQL functions (LOWER, UPPER, TRUNC, ROUND, ...) as part of a query instead of in Python after fetching. Cast(expression, output_field) performs a real database-level type conversion (not just a Python-side type hint, unlike ExpressionWrapper). Coalesce(*expressions) returns the first non-NULL value among its arguments — the database-side equivalent of a Python or-chain (a or b or c or default).

Key features

Cast(expression, output_field=SomeField()) performs a REAL database-level conversion — unlike ExpressionWrapper, which only tells the ORM how to interpret an existing value in Python
Coalesce(*expressions, output_field=...) returns the first non-NULL argument, left to right — commonly used to substitute a fallback for a NULL column: Coalesce(F("nickname"), F("first_name"))
Date functions (Trunc/TruncDate/TruncMonth/Extract/Now, etc.) let you group or filter by a date component computed inside the query, not after fetching
String functions (Lower/Upper/Length/Trim/Concat, etc.) run the equivalent SQL function on a column, usable inside filter()/annotate()/order_by()
Numeric functions (Round/Abs/Ceil/Floor/Power, etc.) do the same for arithmetic — useful for computed, filterable numeric columns without loading rows into Python first

Database function families

Database function families
Family	Examples	Typical use
Type conversion	Cast	compare/store a value as a different DB type
Null handling	Coalesce	substitute a fallback for a NULL column, in the query
Date/time	TruncMonth, Extract, Now	group by month/year, filter by a date component
String	Lower, Upper, Concat, Length	case-insensitive comparisons, computed text columns
Numeric	Round, Abs, Ceil, Floor	computed numeric columns, usable in filter()/order_by()

Command

python

Model.objects.annotate(safe_value=Coalesce(F("maybe_null"), Value(default)))
Model.objects.annotate(converted=Cast("text_column", output_field=IntegerField()))

Coalesce is the database-side equivalent of a Python "or" fallback chain — put it INSIDE the query (annotate/filter/order_by), not applied to already-fetched values, or ordering/filtering on the fallback breaks. Cast performs a real database-level type conversion; ExpressionWrapper does not.

Spotted a mistake on this page? Tell us.

Read Sheet