Field Expressions (F Objects)

F() expressions enable database-side field references and arithmetic. They let you compare fields to other fields, perform calculations in SQL, and create computed columns — all without pulling data into Julia.

When to Use F Expressions

Core Syntax

using PormG: F
using PormG.Functions: Count, Sum, Max, Min

# Field reference
F("grid")

# Arithmetic
F("points") + 5                   # Addition
F("points") - F("grid")          # Subtraction
F("points") * F("laps")          # Multiplication
F("points") / 2.0                # Division
Sum("points") / Count("resultid") # Aggregate ratios

Supported arithmetic operators: +, -, *, / with numeric or field operands. For date arithmetic, + and - also accept Julia Dates durations (Day, Month, Year, …) and the Interval helper — see Date Arithmetic. A duration is also an interval value: * scales one by a number, and / divides one placed first — see A duration as an interval value.

A numeric operand is an Integer, a Float64, a Float32 or a Float16 (the last two bind as the Float64 they hold), or Value(n) of one, which is the same literal: F("grid") + Value(1) is F("grid") + 1. A number goes on either side, 10 - F("grid") and 100 / F("laps") included, and keeps its place. A Decimal or a Rational raises a QueryBuildError naming the spelling to use: Float64(x), or a cast that keeps a decimal's digits, Cast(Value("2.5"), "numeric") (#1476).

On the left of an F(...) expression, a function, a window function or a CTE(...) column, two more operands are taken, each where it means something: a date or timestamp literal on the left of - (Date(2009, 3, 29) - F("date"), see Subtracting two dates), and a duration. A duration on the left of + or * is the duration on the right (Day(1) + F("date") is F("date") + Day(1)), and on the left of - or / it is an interval value (Minute(2) - F("time"), see A duration as an interval value). A function or a window function refuses a duration on either side. A date on the left of +, * or /, and missing (or nothing) on either side, raise a QueryBuildError that names what the left takes (#1549). missing is SQL NULL, and arithmetic with NULL is always NULL: to count a missing value as a number, coalesce the column, Coalesce("grid", 0) + 1.

q = M.Result.objects
q.values("resultid", "behind" => 20 - F("positionorder"))   # (? - "Tb"."positionorder")

Division keeps the type of its operands, on every engine. Two integers divide as integers: on M.Result, F("grid") / 2 is 7 for a grid of 15, as in Django, PostgreSQL and SQLite. MySQL's own / keeps the fraction, so PormG renders DIV there. A float or a decimal on either side makes the quotient a fraction: F("points") / 2 (a FloatField there) and F("grid") / 2.0 are 7.5. Over a DecimalField, such as M.Constructor_results' points, or over a value that is one of several where one is a fraction (Coalesce("grid", 0.5), a Case with an integer and a float branch, a window with a fractional default), SQLite would hold an integer on some rows — it stores a whole decimal (15.00) as one — and divide it as an integer. So PormG casts the dividend to REAL there: F("points") / 2 on M.Constructor_results renders (CAST("Tb"."points" AS REAL) / ?) on SQLite and is 7.5 on every engine (#1150). A float column or a float literal is a REAL already, and renders as written.

Comparison Operators

F() expressions support standard Julia comparison operators, translated directly to SQL:

These are most useful when the comparison involves two columns or a computed expression — cases where the suffix filter API cannot help.

A function on the left

The same operators work with a SQL function on the left, such as Lower or Upper. The comparison becomes a WHERE predicate, and the value on the right is a bound parameter:

using PormG.Functions: Lower

query = M.Driver.objects
query.filter(Lower("surname") == "senna")
query.values("forename", "surname")
df = query |> DataFrame

Generated SQL:

SELECT "Tb"."forename" as "forename", "Tb"."surname" as "surname"
FROM "driver" as "Tb"
WHERE (LOWER("Tb"."surname") = $1::text)

Against the F1 data this returns Ayrton Senna and Bruno Senna. The right-hand side takes what an F comparison takes: a literal, a column reference such as F("forename"), a scalar Subquery(...) (see A Subquery as a Filter Value), or another function. Lower("forename") == Lower("surname") and F("points") > Coalesce("grid", 0) render as the filter pair "points__@gt" => Coalesce("grid", 0) does (#1514). An aggregate or a window function on the right is refused in filter, as it is on the left.

An aggregate on the left (Count("resultid") > 10) builds an expression too, but filter refuses it, because an aggregate cannot be a WHERE predicate. Filter on an aggregate through an alias instead. See In Filters (Auto-HAVING).

A window function on the left (Rank(over = w) <= 3) builds one as well, and filter refuses it for the same reason: SQL evaluates windows after WHERE. The comparison is still useful inside a projection, as the condition of a Case/When. To filter on a window, see Filtering on a Window Result.

Arithmetic is not a condition

An arithmetic or bitwise expression is a value, not a condition. Wherever PormG expects a condition — filter(...), Q(...)/Qor(...), a When(...) branch, on(...) — it refuses F("laps") + 1, F("points") & 4 or ~F(...) at construction with a QueryBuildError. Compare the value instead:

using PormG.Functions: Case, When

query = M.Result.objects
query.filter("raceid__year" => 2009)
query.values("driverid__surname", "laps",
             "past_50" => Case([When((F("laps") - 50) > 0, then = 1)], default = 0))

The refusal is there because a number does not say which rows it means, so it is refused on every engine: the comparison you write is the condition. (Left to the engines, one rejects a number used as a condition and another reads it as true when it is non-zero.) A bare boolean column is a condition already and keeps working, for example filter(F("is_active")) or When(F("is_active"), then = 1). Arithmetic stays legal anywhere a value goes: in a projection, in then, and on the right of a comparison.

A function is a When condition only when its result is boolean, for the same reason. When(Lower("surname"), then = 1) raises QueryBuildError, and so do Length, Sum, Rank, a Cast to a non-boolean type, and a Coalesce or Max over a number or text column. Compare the function instead, as in When(Lower("surname") == "senna", then = 1). A function whose result is boolean keeps working:

using PormG.Functions: Case, When, Cast

query = M.Result.objects
query.filter("raceid__year" => 2009)
query.values("driverid__surname", "laps",
             "started" => Case([When(Cast("laps", "boolean"), then = 1)], default = 0))

A boolean result is recognized from a Cast(…, "boolean"), an output_field = "boolean", a BooleanField operand (Coalesce(F("is_active"), false)), or a condition operand (Max(Q("grid__@gt" => 1))). PormG checks the result type when it can name it: Lag over a column has the column's type, and a Case with no output_field the type its branches agree on, so When(Lag("grid", over = …)) is refused as a number. A function whose type it cannot name, such as a Case whose branches have different types, is not checked here and renders as written; beside a date or a duration, SQLite and MySQL refuse it (A value PormG cannot type beside a date or a duration).

Expressions are values, not builders

Every operator — comparison and arithmetic alike — returns a new expression and leaves its operands untouched. So an F() handle can be bound to a name and reused across as many predicates, queries and projections as you like:

pts = F("points")

query = M.Result.objects
query.filter(pts > 10, pts < 25)          # two independent predicates on the same column
query.values("driverid__surname", "bonus" => pts * 0.1)
df = query |> DataFrame

Generated SQL:

SELECT "Tb_1"."surname" as "driverid__surname",
       ("Tb"."points" * $1::double precision) as "bonus"
FROM "result" as "Tb"
 INNER JOIN "driver" AS "Tb_1" ON "Tb"."driverid" = "Tb_1"."driverid"
WHERE ("Tb"."points" > $2::bigint) AND ("Tb"."points" < $3::bigint)

Each use of pts renders as the bare column it names — three independent references, not one nested inside another. This is the same contract as Django's F() and SQLAlchemy's column expressions.

pts, grid = F("points"), F("grid")

isequal(pts, pts)                     # true
isequal(pts, F("points"))             # false — a distinct handle, even for the same column
pts in Set([pts, grid])               # true
length(unique([pts, pts, grid]))      # 2
findfirst(isequal(grid), [pts, grid]) # 2
pts == grid                           # an expression, ready for filter(...)

F Expressions in Filters

Field-to-Field Comparison

Find results where the starting grid position matches the finishing position:

query = M.Result.objects
query.filter(F("grid") == F("positionorder"))
df = query |> DataFrame

Generated SQL:

WHERE "Tb"."grid" = "Tb"."positionorder"

Relationship-Aware Comparison

Compare fields across joined tables. Find results where the driver's birth month matches the race month:

query = M.Result.objects
query.filter(
    F("driverid__dob__@month") == F("raceid__date__@month")
)
df = query |> DataFrame

PormG resolves the __ paths, creates the necessary joins, and applies the EXTRACT(MONTH FROM ...)::integer transform (PostgreSQL; CAST(strftime('%m', ...) AS INTEGER) on SQLite; EXTRACT(MONTH FROM ...) on MySQL) on both sides.

Comparing Against a Literal

The right-hand side of an F comparison may be an integer or a float, a string, a Base.UUID, or one of the temporal types Dates.Time, Dates.Date, Dates.DateTime and TimeZones.ZonedDateTime. Whatever the literal, it is bound as a parameter through the column's own formatter — the same path an ordinary filter pair takes — so F("dob") >= Date(1970, 1, 1) and "dob__@gte" => Date(1970, 1, 1) bind identical values on every backend. That matters most on SQLite, where a column holds the text its formatter produced: a Float64 against a FloatField binds "1.5", a Bool against an IntegerField binds 1, and a UUID binds its canonical string.

using Dates

query = M.Driver.objects
query.filter(F("dob") >= Dates.Date(1970, 1, 1))
query.values("surname", "dob")
df = query |> DataFrame

Generated SQL:

WHERE ("Tb"."dob" >= $1)      -- $1 bound as '1970-01-01'

A value of any other type — a Rational, a byte vector, nothing — raises QueryBuildError at the comparison itself, naming the type and the accepted ones. With the F on the left, a comparison never silently evaluates to a Bool. To test for NULL, filter with "col__@isnull" => true instead.

For a plain column-against-literal test like this one, the suffix form is the idiomatic spelling — see When NOT to Use F. Where F earns its place is when the left side is an expression the suffix API cannot build. Putting date arithmetic on the left and a date literal on the right is the clearest case:

# Drivers who had already turned 18 by the first World Championship race (13 May 1950)
query = M.Driver.objects
query.filter(F("dob") + Dates.Year(18) <= Dates.Date(1950, 5, 13))
query.values("surname", "dob")
df = query |> DataFrame

Generated SQL (PostgreSQL):

WHERE ((("Tb"."dob" + make_interval(years => $1::integer)))::date <= $2)

On SQLite the same expression renders through date(...) modifiers instead — date("Tb"."dob", '+' || ? || ' years') <= ? — and binds the same two values. On MySQL it renders ((`Tb`.`dob` + INTERVAL ? MONTH) <= ?), binding the 18 years as one count of 216 months.

The representation follows the column, not the value: a DateField binds the calendar date ('1970-01-01'), a DateTimeField binds the canonical UTC timestamp ('1970-01-01T00:00:00.000+00:00'), and a DateTime compared against a DateField is coerced to its calendar date — the same coercions filter("dob" => value) applies. That is what keeps the two spellings interchangeable on SQLite, where dates are compared as text.

Mixing F and Standard Filters

Combine F() comparisons with ordinary filter pairs when only part of the predicate needs an expression:

query = M.Result.objects
query.filter(
    F("driverid__dob__@day") == F("raceid__date__@day"),
    F("driverid__dob__@month") == F("raceid__date__@month"),
    "positionorder__@lte" => 10,   # Standard scalar filter
)
df = query |> DataFrame

Date Arithmetic

Offset a date column by a whole number of days with a bare integer — e.g. results within 30 days of the driver's birthday:

query = M.Result.objects
query.filter(
    F("raceid__date") > F("driverid__dob"),
    F("raceid__date") <= F("driverid__dob") + 30   # integer ⇒ days
)

A bare integer and an explicit Day(n) are the same thing: the integer is whole days, and both render through the same path, so they compose identically and at any depth — (F("date") + 7) + 3 shifts by ten days, exactly as F("date") + Day(7) + Day(3) does.

The date side can be any expression whose type is a date, not only a column: Max("date"), Coalesce("date", …), Lag("date", over = …), or a Case whose branches are all dates. Case(When("round" => 1, then = F("date")), default = F("date")) + 1 is the date one day later on every engine; a Case that mixes a date and a timestamp must say its type (output_field).

Generated SQL (PostgreSQL) — both operands are joined columns, so both carry a join alias:

WHERE ("Tb_1"."date" > "Tb_2"."dob")
  AND ("Tb_1"."date" <= (("Tb_2"."dob" + make_interval(days => $1::integer)))::date)
-- parameters: [30]

Generated SQL (SQLite):

WHERE ("Tb_1"."date" > "Tb_2"."dob")
  AND ("Tb_1"."date" <= date("Tb_2"."dob", '+' || ? || ' days'))
-- parameters: [30]

Generated SQL (MySQL):

WHERE (`Tb_1`.`date` > `Tb_2`.`dob`)
  AND (`Tb_1`.`date` <= (`Tb_2`.`dob` + INTERVAL ? DAY))
-- parameters: [30]

Explicit duration types

For anything other than whole days, add a Julia Dates duration — Day, Week, Month, Year, Hour, Minute, Second, or any CompoundPeriod (e.g. Month(1) + Day(15)). A date takes a duration under + and -, on the right or on the left of + (Day(30) + F("date") is F("date") + Day(30)). A duration minus a date has no meaning. A duration is also an interval value of its own; see A duration as an interval value below:

using Dates

# Push the Australian GP back by a month and a half (parenthesise the compound period
# so it renders as one interval — a bare `+ Month(1) + Day(15)` chains two instead):
M.Race.objects.filter("raceid" => 1).update("date" => F("date") + (Month(1) + Day(15)))

# Roll every race back a year:
M.Race.objects.update("date" => F("date") - Year(1))

Generated SQL (PostgreSQL) — a single, strongly-typed make_interval(...), cast back to date because the column is a DateField (see the note on the promotion rule below):

(("Tb"."date" + make_interval(months => $1::integer, days => $2::integer)))::date
-- parameters: [1, 15]

Generated SQL (SQLite) — date(), one modifier per component:

date("Tb"."date", '+' || ? || ' months', '+' || ? || ' days')
-- parameters: [1, 15]

Generated SQL (MySQL) — one INTERVAL per component, years and months as one month count:

((`Tb`.`date` + INTERVAL ? MONTH) + INTERVAL ? DAY)
-- parameters: [1, 15]

Magnitudes are always bound as parameters; the unit keywords come from a fixed whitelist. Weeks render natively on PostgreSQL and MySQL (INTERVAL ? WEEK) and as days (×7) on SQLite, which has no weeks modifier.

When the result is a timestamp rather than a date — a DateTimeField column, or any duration carrying hours, minutes or seconds — SQLite renders the same modifiers through strftime with PormG's canonical UTC mask instead:

strftime('%Y-%m-%dT%H:%M:%f+00:00', "Tb"."created_at", '+' || ? || ' days')

That mask is the exact format a DateTimeField stores, which is the point of it: SQLite compares timestamps as text, so an expression rendered in SQLite's own YYYY-MM-DD HH:MM:SS form could never equal a stored value — and, because a space sorts below T, would always compare less than it. Rendering the arithmetic straight into the stored representation keeps ==, <, >, ORDER BY and update() all agreeing with PostgreSQL. You never write the mask yourself.

The Interval helper

Interval(...) is an explicit, self-documenting wrapper. It accepts a period (identical to using the bare period) or a portable time-duration string ("HH:MM:SS", "M:SS", or bare seconds):

F("date") + Interval(Month(3))            # same as F("date") + Month(3)

# The string form is for time-of-day intervals on a TIMESTAMP / DateTimeField column:
F("created_at") + Interval("01:30:00")    # + 1 hour 30 minutes

Subtracting two dates

Subtracting one DateField expression from another gives the number of days between them, as an integer, on every engine — each driver's age in days on the day of the race, and only the results scored after their 30th birthday:

query = M.Result.objects
query.filter("raceid__year" => 2009, (F("raceid__date") - F("driverid__dob")) > 30 * 365)
query.values("driverid__surname", "raceid__name", "age_days" => F("raceid__date") - F("driverid__dob"))

Generated SQL (PostgreSQL) — its own date - date already returns an integer day count:

("Tb_2"."date" - "Tb_1"."dob")

Generated SQL (SQLite) — a date is stored as text there, so the difference goes through julianday:

CAST(julianday("Tb_2"."date") - julianday("Tb_1"."dob") AS INTEGER)

Generated SQL (MySQL) — its own - on two dates subtracts the numbers their digits spell, so the difference is DATEDIFF:

DATEDIFF(`Tb_2`.`date`, `Tb_1`.`dob`)

The result is a plain number, so it compares against an integer (> 30 * 365 above) and composes with ordinary arithmetic. A whole-day shift is still a date, so (F("date") + Day(30)) - F("date") is 30.

A timestamp difference is the time from one instant to the other — here, how long after midnight each 2009 race started:

query = M.Race.objects
query.filter("year" => 2009)
query.values("name", "since_midnight" => F("start_at") - F("date"))

PostgreSQL renders ("Tb"."start_at" - "Tb"."date"), an interval. SQLite has no interval type, so the difference is rendered as the text a DurationField stores there — 06:00:00, with hours never folded into days (126:00:00) and a fraction only when there is one — and MySQL renders (-TIMESTAMPDIFF(MICROSECOND, `Tb`.`start_at`, `Tb`.`date`)), the microseconds a DurationField stores there. All three read back as the same Dates.CompoundPeriod (== Hour(6)).

Compare a timestamp difference against a duration:

query = M.Race.objects
query.filter("year" => 2009, (F("start_at") - F("date")) == Hour(6))

All six comparisons work on every engine — here, the 2009 races that started after noon UTC:

query = M.Race.objects
query.filter("year" => 2009, (F("start_at") - F("date")) > Hour(12))
query.values("name")

A timestamp difference also takes interval arithmetic: + and - with another difference, a DurationField or a duration (Hour(1)), and * or / by a number (d * 2, 2 * d, F("points") * d, d / 2). How far each 2009 start was from 14:00 UTC, negative when earlier:

query = M.Race.objects
query.filter("year" => 2009)
query.values("name", "from_14h" => (F("start_at") - F("date")) - Hour(14))

A date or timestamp plus or minus an interval value — a difference or a DurationField — is a timestamp, with the date on either side. When each driver's first lap of the 2009 Australian GP ended:

query = M.Lap_times.objects
query.filter("raceid__year" => 2009, "raceid__round" => 1, "lap" => 1)
query.values("driverid__surname", "ended_at" => F("raceid__start_at") + F("time"))

SQLite has no interval type, so PormG computes an interval there as a whole number of milliseconds and turns only the finished value into the DurationField text. Comparisons are numeric, so a difference of 100 hours or more, and a negative one, order correctly. Two limits follow from it, and both are intentional divergences:

• Precision is the millisecond on SQLite, the precision a stored timestamp has; PostgreSQL and MySQL keep microseconds. Dividing by zero gives NULL on SQLite and MySQL and an error on PostgreSQL.
• What has no millisecond form is refused on SQLite. A month or a year (d + Month(1)) has no fixed length, and raises BackendCapabilityError (the calendar_intervals row in Backends). A window function inside the interval (F("start_at") - Lag("start_at", over = …)) raises QueryBuildError. The window sees a single row in the subquery that formats the text; project its value in a CTE first and use the column. Max/Min over a timestamp are fine. A Case whose value is a duration, or a function over one (Sum, Max, Coalesce, …), is refused in arithmetic too (Case(When("lap" => 1, then = F("time")), default = F("time")) * 2). SQLite holds its value as the HH:MM:SS text, and multiplying that text gives 0. Put the arithmetic inside each branch instead (Case(When("lap" => 1, then = F("time") * 2), default = F("time") * 2)). On PostgreSQL and MySQL the arithmetic over the Case builds and reads back as a Dates.CompoundPeriod. MySQL also refuses the month or the year, which has no fixed count of microseconds, and builds the window function: it holds an interval as its microseconds, with no text to format.

Some shapes have no meaning on any engine: d * d, d / d, F("points") / d, a number plus or minus an interval (d + 5, count + d), and a date times or divided by an interval. They raise QueryBuildError when the query is built, on PostgreSQL as on SQLite and MySQL. Comparing a number with an interval is not arithmetic, and is not refused here. A CTE interval column on the right of a float or text column is not refused yet (#1581).

A duration as an interval value

A Dates duration (or an Interval) is an interval value, as a DurationField is. So it takes the same arithmetic, with the duration on either side:

Here lap is an IntegerField and time a DurationField. Each lap's time against a pace of 1:30 per lap, negative when faster:

query = M.Lap_times.objects
query.filter("raceid__year" => 2009, "raceid__round" => 1, "lap__@lte" => 3)
query.values("driverid__surname", "lap", "time",
             "vs_pace" => F("time") - Second(90),
             "pace_total" => F("lap") * Second(90))

Generated SQL (PostgreSQL), where the duration is a make_interval:

("Tb"."time" - make_interval(secs => $1::double precision)) as "vs_pace",
("Tb"."lap" * make_interval(secs => $2::double precision)) as "pace_total"
-- parameters: [90.0, 90.0, 2009, 1, 3]

On SQLite the duration binds as its milliseconds (90000), and on MySQL as its microseconds (90000000). A product or a quotient is rounded back to a whole unit, as it is for a DurationField column. The result reads back as a Dates.CompoundPeriod on every engine.

A duration on the left of + and * commutes. On the left of - and / it stays where it was written. The rest has no meaning and raises QueryBuildError on every engine, PostgreSQL included:

• a number or an interval divided by a duration (F("grid") / Hour(1)); write the duration first, Hour(1) / F("grid");
• a duration minus a number or a date, or divided by an interval (but see the note on untyped sides below);
• a date, a text column or an interval multiplied by a duration.

The refusal is decided by the side's type. A CTE(...) column has its column's type, on either side: F("grid") * CTE("c", "u") over a duration column scales it as F("grid") * F("time") does, and F("points") + CTE("c", "u") is refused as F("points") + F("time") is. A side PormG cannot type is taken as a number, as it is for a DurationField column (d * x). A side that is meaningless interval arithmetic itself, such as F("time") / F("time") or F("time") + 5, raises QueryBuildError where it is built, on every engine.

A month or a year has no fixed length, so it is an interval value only on PostgreSQL, where an interval keeps months apart: F("grid") * Month(1) is make_interval(months => …) there. SQLite and MySQL raise BackendCapabilityError (calendar_intervals in Backends). A date shifted by a month (F("date") + Month(1)) is not a value, and builds on every engine.

A month value is multiplied by a whole number only, and never divided. F("grid") * Month(1) is seven months for a grid of 7. A month has 28 to 31 days, so 1.5 months, or a year over 7, has no single answer. On every engine, QueryBuildError is raised for:

• a month multiplied by a float column, a DecimalField with decimal places, or arithmetic that is not whole ((F("grid") * 1.5) * Month(1));
• a month divided by anything (Year(1) / F("grid")).

(PostgreSQL would turn the fraction into days at 30 days a month.) The side's type decides, not its value. Whole means an integer column, integer arithmetic, or a DecimalField with decimal_places = 0. Integer division is whole too: (F("grid") / 2) * Month(1) takes the integer quotient.

The rule follows the month wherever PormG can see it. That includes F arithmetic ((F("grid") * Month(1)) * 1.5, (F("time") + Month(1)) / 2) and any value that is one of its operands' (#1583): Max/Min/Sum, Coalesce, Greatest/Least, NullIf, Case (a month literal among its values too, then = Month(1)), a Cast to "interval", a window function such as Lag, and a CTE column or a Subquery that projects a month value. So Max(F("grid") * Month(1)) * 1.5 is refused too, while Max(F("grid") * Month(1)) * 2 builds. A stored interval (a DurationField column, or a CTE or Subquery column over one) can hold months on PostgreSQL, but its type does not say so, so PormG does not refuse scaling it: F("time") * 1.5 and Max(F("time")) * 1.5 build.

A float column cannot be cast to an integer inline here: a function takes no duration. To scale by one, cast it to an integer in a CTE column and multiply that column. Or use a duration of fixed length, which any number scales: Day(45) * F("points").

Only F(...) expressions and CTE(...) columns take a duration this way. A function or a window function takes none: shift or scale the column inside the function (Max(F("time") - Second(1))).

A number function over a duration (Round, Floor, Ceil, Sqrt, Exp, Ln, Power, Mod, Abs) raises QueryBuildError on every engine when the query is built (#1324): a duration is not a number, so none of them has a meaning over one. PostgreSQL raised at the server, SQLite computed on the HH:MM:SS text (Round(F("fastestlaptime")) read 0.0), and MySQL on the count of microseconds. For the magnitude of an interval, write Greatest(d, d * -1). For its seconds, write Extract(d, "epoch") on PostgreSQL and MySQL, and apply the function to that; SQLite has no EPOCH part. Sum and Avg mean a duration's total and mean, and keep building on every engine. A date, a timestamp, a time or a UUID is refused under all of them, Sum and Avg included (#1387; see Mathematical Functions).

Every part but EPOCH of a duration expression raises QueryBuildError on every engine, as over a DurationField column (Which columns a transform reads). A calendar part (Extract(F("fastestlaptime") * 2, "year"), or Extract(…, "quarter") and ToChar(…, "YYYY-MM") over a timestamp difference or a Sum of a duration) has no meaning, because a duration has no calendar date. A time-of-day part (Extract(F("start_at") - F("date"), "hour"), a Sum of a duration under Extract(…, "second")) has no single meaning (#1388): PostgreSQL reads a component that depends on how the interval was formed (a 62-hour timestamp difference reads hour 14, a 208-hour sum reads 208), SQLite read NULL for a duration over 24 hours and for a negative one, and MySQL would read its count of microseconds as a time. For a duration's seconds, write Extract(d, "epoch") on PostgreSQL and MySQL; SQLite has no EPOCH part.

Ordering, filtering and the extremum of the projected value are numeric on SQLite too. A query still returns the text, but PormG sorts and compares the milliseconds behind it. Sorting the text would be right only below 100 hours, where "100:00:00" sorts before "99:00:00", and wrong for negative values. These cases use the milliseconds:

• order_by on an interval's alias, and a filter on that alias, bound as milliseconds. A filter can be "since_midnight__@gt" => Hour(12), @range, @in or ==, or compare against another interval expression ("since_midnight__@gt" => F("start_at") - F("date") - Hour(1)).
• Max and Min over an interval: a difference, interval arithmetic or a DurationField. They return the longest and the shortest value, read back as a Dates.CompoundPeriod on every engine.
• Sum and Avg over an interval, which return the total and the mean duration, as PostgreSQL's sum(interval) and avg(interval) do. On SQLite the mean is rounded to the millisecond, and on MySQL to the microsecond. Both read back as a Dates.CompoundPeriod on every engine.
• Greatest, Least and Coalesce when every argument is a difference, interval arithmetic, a DurationField (by path, F(...) or Joined(...)), one of the functions in this list, a duration literal (Coalesce("time", Value(Hour(0))), the usual "default to zero"), or a NULL literal. Any other argument keeps the text comparison: a text column, a CTE(...) column, a Subquery(...), or a duration literal with a month, a year or a fraction of a millisecond. So does a declared output_field.
• A labelled order term of an interval expression, which orders by the expression without projecting it: order_by(SQLOrder(SQLField(F("time") * 2, "x"))), or a DurationField written F("time"). Grouped, its GROUP BY copy keeps the text and binds its own values.
• A duration literal projected under an alias (values("v" => Value(Hour(100)))) and filtered by an ordering lookup ("v__@gt" => Hour(99)), both sides bound as milliseconds. A literal with a fraction of a millisecond has no millisecond form, so ordering it is refused on SQLite. Equality keeps the text. A literal with a month or a year cannot be a SQLite parameter at all.
• A When condition on an interval's alias, as a filter on it is (Case(When(Q("total__@gt" => Hour(1)); then = 1); default = 0)). The alias must come before the Case in values(...).
• A DurationField column that is ordered (order_by("time")), compared with an ordering lookup ("time__@gt" => Minute(2), @range), or written as F("time") > Minute(2). Its == and @in compare the stored text. That is exact, because every write stores the canonical HH:MM:SS form (since #891), and an index on the column still serves them.

Like the arithmetic above, these comparisons round each side to the nearest millisecond on SQLite.

On PostgreSQL a duration literal binds as an interval ($1::interval), wherever it is written: a Value(Hour(1)), a Case branch, a Coalesce argument. It compares as an interval and reads back as a Dates.CompoundPeriod, as a DurationField does. Without the type, two such parameters were compared as text ("5 hours" > "40 hours"), and a Case over them read back as its text. On MySQL a duration literal binds as its count of microseconds, as a DurationField holds it.

The latest 2009 starts, by time of day:

query = M.Race.objects
query.filter("year" => 2009)
query.values("name", "since_midnight" => F("start_at") - F("date"))
query.order_by("-since_midnight")

On SQLite the ORDER BY repeats the difference in milliseconds rather than naming the since_midnight text, and it binds that expression's own parameters again.

Some shapes have no millisecond form on SQLite, which holds them as their HH:MM:SS text. That text sorts in the duration's order only below 100 hours and for non-negative values, so ordering one is refused on SQLite with QueryBuildError, however it is spelled:

• inside an expression: filter(Case(…) > Hour(1)), When(Lag("time", over = …) > Hour(1));
• as an ordering filter on its alias: values("d" => Case(…)) then filter("d__@gt" => Hour(1)), or @gte, @lt, @lte, @range;
• as order_by("d") on its alias, or as a labelled order term of the expression itself (order_by(SQLOrder(SQLField(Case(…), "d")))).

Equality ("d" => Hour(1), "d__@in" => […]) still compares the text, as it does for a DurationField. PostgreSQL compares the interval itself, and MySQL its count of microseconds, and both build all of them. To order or filter one on SQLite, project it in a CTE (.with("c" => body, …)) and order or filter the CTE's column (order_by("-c__d"), "c__d__@gt" => Hour(1)), which SQLite reads in milliseconds.

The shapes:

• Case whose value is a duration, and NullIf, Max or another function over one. Its When conditions compare milliseconds (see above). A Case's value reads back as the stored text.
• A window value function over a duration (Lag("time", over = …)), including one over a Subquery(...). Its value reads back as a Dates.CompoundPeriod.

On SQLite, a Subquery(...) that returns a duration (#888) is ordered and compared by its milliseconds, and its value reads back as a Dates.CompoundPeriod. But Greatest and Least over one choose their result by the text, so past 100 hours or below zero the value itself can be the wrong argument.

An aggregate over an interval can be used inside arithmetic too. On SQLite it stays a number of milliseconds until the whole expression is finished, as a difference does. Sum(d) / Count("id") is a mean duration, Sum("time") - Max("time") the total without the slowest lap, Max("time") + d an interval, and Count("lap") * Sum("time") a scaled one. A count plus, minus or divided by an interval is refused, as count + d is above. The total race time per driver, less the slowest lap, in the 2009 Australian GP:

query = M.Lap_times.objects
query.filter("raceid__year" => 2009, "raceid__round" => 1)
query.values("driverid__surname", "without_slowest" => Sum("time") - Max("time"))
query.order_by("without_slowest")

A duration compares only against an interval — a timestamp difference or a DurationField. Against anything else (F("date") > Hour(1), or a day count) it raises QueryBuildError. A Time is a time of day, not a duration, and is refused against an interval: write Hour(1) or Minute(90) instead.

+, * and / between two date or timestamp values have no meaning and raise QueryBuildError on every engine. To move a date, add a duration: F("date") + Day(30).

Either side of a difference can be more than a column. An extremum (Max, Min), a window value function (Lag, Lead, FirstValue, …), a __@date path and a date literal each count as the date or timestamp they produce, so the same rules apply — the length of the 2009 season in days, and how many days after the season opener each race was held:

query = M.Race.objects
query.filter("year" => 2009)
query.values("season_days" => Max("date") - Min("date"))

query = M.Race.objects
query.filter("year" => 2009)
query.values("name", "since_opener" => F("date") - Date(2009, 3, 29))

A date literal is a Date, DateTime or ZonedDateTime. A string on the right of date arithmetic is text, not a date (F("date") - "2009-03-29"), and raises QueryBuildError on every engine; a string that names a field (F("date") - "dob") is that field. Subtracting a date literal from something that is not a date or timestamp PormG can type — a number, Sum(...), __@year — raises QueryBuildError too.

A date literal goes on the left of - as well, and the difference is then the other way round: Date(2009, 3, 29) - F("date") is how many days each race was held before the opener, the negative of F("date") - Date(2009, 3, 29), on every engine (#1549). The literal stays on the left in the SQL (($1::date - "Tb"."date") on PostgreSQL), and the rules above apply with the sides swapped: a timestamp literal minus a timestamp is an interval, and a date literal minus a day count is a date shifted back.

query = M.Race.objects
query.filter("year" => 2009)
query.values("name", "before_opener" => Date(2009, 3, 29) - F("date"))

A day count combined with a date is a whole-day shift, as it is on PostgreSQL: F("date") ± count and count + F("date") are dates (timestamps stay timestamps), on every engine. Each 2009 race moved as far again past the season opener:

query = M.Race.objects
query.filter("year" => 2009)
query.values("name", "doubled" => F("date") + (F("date") - Date(2009, 3, 29)))

An integer column (IntegerField, PositiveIntegerField, PositiveSmallIntegerField, BigIntegerField) is a day count too, so it shifts a date the same way on every engine — here, each 2009 race date moved back by its round number:

query = M.Race.objects
query.filter("year" => 2009)
query.values("name", "round", "shifted" => F("date") - F("round"))

PostgreSQL renders ("Tb"."date" - "Tb"."round"); SQLite shifts the julian-day number, date(julianday("Tb"."date") - ("Tb"."round")); MySQL renders (`Tb`.`date` - INTERVAL (`Tb`.`round`) DAY). A BigIntegerField is cast to integer on PostgreSQL, which has date - integer but no date - bigint. Only a plain column counts: a ForeignKey, an ID, F("round") * 2, a float, a text column or Sum(...) beside a date raises QueryBuildError on SQLite, where a date is text and + or - would use only its year, and on MySQL, where + or - reads a date as the number its digits spell. PostgreSQL has no such operator for these either, and fails when the statement runs. A TimeField is the one exception: PostgreSQL's date + time is a timestamp, while SQLite and MySQL raise QueryBuildError for it — an intentional divergence. On SQLite and MySQL, add the time as a duration instead (F("date") + Hour(6)).

A day count minus a date has no meaning and raises QueryBuildError on every engine; subtract the count from the date instead. Shifting a date or timestamp by an interval value — a DurationField or a timestamp difference — gives a timestamp on every engine (see above).

A value PormG cannot type beside a date or a duration

PormG writes the SQL of arithmetic and of an ordering (<, >, <=, >=, @range) beside a date, a timestamp or an interval by its type: a whole-day shift, an interval in milliseconds, a date's text. Some values have no type PormG can name: a JSON key lookup, whose type is the data's, a Case or Coalesce whose values have different types (a duration or 0), or a function over one. On SQLite and MySQL such a value beside a date or a duration raises QueryBuildError when the query is built, and so does one that holds a date or a duration, whatever it is combined with. Those engines compute on what they store (SQLite on a date's or an interval's text, MySQL on the number a date's digits spell), so Case(When("rank" => 1, then = F("fastestlaptime")), default = 0) * 2 would multiply the text '00:01:30' and read 0. Every spelling of the ordering is checked: F(…) > F(…), a filter pair ("date__@gt" => F(…)), a filter on a projected alias, Q(…), a When condition, and an update() value. PostgreSQL types the operands itself, so it builds these shapes, and raises at the server when they have no common type, as that one does. A time of day (TimeField) counts as a date does here (#1323): SQLite compares its HH:MM:SS text and MySQL the number its digits spell. Where the values of a Case or Coalesce are of two types PormG can name, such as a duration and 0, the function is refused before any arithmetic, on every engine (Mixed date, time, text and number operands).

State the type instead: give every value of the Case or Coalesce the one type and do the arithmetic inside each branch, or, where a date is at stake, cast the value (Cast(…, "date")). A duration has no cast on SQLite or MySQL, so write it as one (Second(0), not 0). Twice the fastest lap of each 2009 race's fastest driver, and no time for the others:

query = M.Result.objects
query.filter("raceid__year" => 2009)
query.values("driverid__surname",
             "doubled" => Case(When("rank" => 1, then = F("fastestlaptime") * 2), default = Second(0)))

A value that holds no date or duration is not checked beside a number, and equality and @in are not checked at all: a JSON key times 2, or compared for equality with a date, builds on every engine.

Arithmetic on a time of day

+, -, * and / over a TimeField, with a number or with another time, raise QueryBuildError on SQLite and MySQL (#1323). SQLite stores a time as its HH:MM:SS text and computes on the leading digits, so on the 2009 Australian Grand Prix (06:00:00) F("time") + 1 was 7; MySQL reads a time as the number its digits spell. PostgreSQL has no time + integer either and fails at the server. PormG has no spelling for arithmetic on a time of day on SQLite or MySQL.

On PostgreSQL, a time minus a time is a duration (#1406), typed as a DurationField value is: it reads back as a Dates.CompoundPeriod, compares with a duration (Minute(1)), and is refused where a duration is (a calendar part, a number function such as Round). Its seconds are Extract(F("time") - F("time"), "epoch").

Comparing a time with a time builds on every engine, since the HH:MM:SS text sorts in time order:

query = M.Race.objects
query.filter("year" => 2009, "time__@lt" => Time(12))   # the races that started before noon

When NOT to Use F

For plain scalar comparisons, always prefer the suffix filter API:

# ✅ Preferred
query.filter("points__@gt" => 20, "grid" => 1)

# ❌ Works but not idiomatic
query.filter(F("points") > 20, F("grid") == 1)

F Expressions in values()

Use F() inside values() to create computed columns directly in the SQL SELECT:

query = M.Result.objects
query.filter("statusid__status" => "Finished")
query.values(
    "driverid__surname",
    "points",
    "bonus" => F("points") * 0.1,
    "total" => F("points") + (F("points") * 0.1)
)
df = query |> DataFrame

This generates:

SELECT
    "Tb_1"."surname" as driverid__surname,
    "Tb"."points" as points,
    "Tb"."points" * 0.1 as bonus,
    "Tb"."points" + ("Tb"."points" * 0.1) as total
FROM ...

Aliasing and Calculated Columns

In PormG, you don't need a separate .annotate() method (like Django). Aliases and computed columns are both created in values() using the "alias" => expression pair syntax.

Simple Column Alias (Rename)

query = M.Driver.objects
query.values(
    "full_name" => "surname",   # Rename "surname" to "full_name"
    "code"
)
df = query |> DataFrame
# DataFrame columns: :full_name, :code

Calculated Column (Expression)

query = M.Result.objects
query.values(
    "driverid__surname",
    "bonus_pts" => F("points") * 0.1   # Computed in the database
)
df = query |> DataFrame

Reference vs. Calculation

Aggregate Arithmetic

Aggregate functions (Sum, Count, Avg, Max, Min) can participate in arithmetic. PormG automatically handles the GROUP BY and HAVING implications.

Arithmetic that mixes a plain column with an aggregate, such as F("grid") - Avg("positionorder"), is the exception. The column has to be grouped, and PormG will not group it for you: it raises QueryBuildError unless the query already groups that column. See A Column Beside an Aggregate in One Expression.

In Projections

query = M.Result.objects
query.values(
    "driverid__surname",
    "points_per_result" => Sum("points") / Count("resultid")
)
df = query |> DataFrame

With a Scalar Constant

An aggregate can also be combined with a plain number, and the math still runs in the database. Build the aggregate first, then apply the operator to the result:

# Total points per constructor, minus a flat 10-point penalty
query = M.Result.objects
query.values(
    "constructorid__name",
    "net_points" => Sum("points") - 10
)
df = query |> DataFrame

Generated SQL (PostgreSQL):

SELECT
    "Tb_1"."name" as "constructorid__name",
    (SUM("Tb"."points") - $1::bigint) as "net_points"
FROM "result" as "Tb"
INNER JOIN "constructor" AS "Tb_1" ON "Tb"."constructorid" = "Tb_1"."constructorid"
GROUP BY 1
-- Parameters: [10]

The constant is parameterized ($1), never interpolated into the SQL string — so it stays safe even when the value comes from user input. (On SQLite and MySQL the placeholder is ? instead of $1.)

In Filters (Auto-HAVING)

When a filter references an aggregate alias, PormG moves it to the HAVING clause:

query = M.Result.objects
query.values(
    "constructorid__name",
    "avg_perf" => Sum("points") / Count("resultid")
)
query.filter("avg_perf__@gt" => 5)
df = query |> DataFrame

PormG generates:

SELECT "constructorid__name", SUM("points") / COUNT("resultid") as avg_perf
FROM ...
GROUP BY 1
HAVING SUM("points") / COUNT("resultid") > $1

query = M.Lap_times.objects
query.values("raceid")
query.filter((Max("milliseconds") - Min("milliseconds")) > 3_600_000)   # QueryBuildError

query = M.Lap_times.objects
query.values("raceid", "spread" => Max("milliseconds") - Min("milliseconds"))
query.filter("spread__@gt" => 3_600_000)   # races whose slowest lap is over an hour slower than the fastest
query.order_by("raceid")
df = query |> DataFrame

SELECT "Tb"."raceid" as "raceid",
       (MAX("Tb"."milliseconds") - MIN("Tb"."milliseconds")) as "spread"
FROM "lap_times" as "Tb"
GROUP BY 1
HAVING (MAX("Tb"."milliseconds") - MIN("Tb"."milliseconds")) > $1
ORDER BY "raceid" ASC NULLS LAST

F Expressions in Updates (Write Side)

F expressions are essential for atomic updates — modifying a column based on its current value without a read-modify-write cycle. This prevents race conditions and is usually more performant.

Simple Arithmetic

# Increment points by 1
M.Result.objects.filter("resultid" => 1).update("points" => F("points") + 1)

# Apply a 10% penalty
M.Result.objects.filter("points__@gt" => 10).update("points" => F("points") * 0.9)

Copy Column Values

Set one column equal to another column in the same row:

M.Just_a_test_deletion.objects.
    filter("id" => 5).
    update("test_result2" => F("test_result"))

Complex Expressions

Combine multiple F() expressions in a single update:

M.Just_a_test_deletion.objects.update(
    "test_result2" => (F("test_result2") * 2) + F("test_result")
)

Updates with Join-Based Filters

Filter by joined fields while updating the main table:

# Add 10 bonus points for all British drivers in result 1
M.Result.objects.filter(
    "driverid__nationality" => "British",
    "resultid" => 1
).update("points" => F("points") + 10)

Bitwise Operations

PormG supports direct database-side bitwise operations on integer columns through F() expressions, aggregate FObjects, and WindowFunctions. This is extremely useful for atomic updates, bitwise filtering, and projection of bitmask or flag fields directly within the database without transferring entire tables into memory.

What is a Bitmask?

A bitmask is a technique that uses the individual bits of a single integer value to store multiple, independent boolean flags (yes/no states) simultaneously. Since each bit can be either 0 or 1, an 8-bit integer can store 8 separate flags, and a 64-bit integer can store 64 flags!

Each flag is assigned a specific bit position corresponding to a power of 2:

• Bit 1 (value $1 = 2^0$): Flag A (e.g. is_active)
• Bit 2 (value $2 = 2^1$): Flag B (e.g. is_verified)
• Bit 3 (value $4 = 2^2$): Flag C (e.g. is_admin)
• Bit 4 (value $8 = 2^3$): Flag D (e.g. has_billing)

How Binary Columns Work Under the Hood

In standard decimal (base-10), we count using powers of 10 (ones, tens, hundreds, thousands...). In binary (base-2), computers count using powers of 2.

An 8-bit integer is represented as 8 binary slots counted from right to left:

Bit Position:    8th   7th   6th   5th   4th   3rd   2nd   1st
Power of 2:      2^7   2^6   2^5   2^4   2^3   2^2   2^1   2^0
Decimal Value:   128    64    32    16     8     4     2     1
               ┌─────┬─────┬─────┬─────┬─────┬─────┬─────┬─────┐
Binary Slots:  │  0  │  0  │  0  │  0  │  1  │  0  │  0  │  0  │
               └─────┴─────┴─────┴─────┴─────┴─────┴─────┴─────┘
                                          ▲
                                      This is Bit 4!
                                      (Value = 8)

If a user has both is_active (value 1) and has_billing (value 8) enabled, their status integer value in the database will be: $8 + 1 = 9$

In binary representation, status = 9 looks like this:

Bit Position:    8th   7th   6th   5th   4th   3rd   2nd   1st
Decimal Value:   128    64    32    16     8     4     2     1
               ┌─────┬─────┬─────┬─────┬─────┬─────┬─────┬─────┐
User's Status: │  0  │  0  │  0  │  0  │  1  │  0  │  0  │  1  │
               └─────┴─────┴─────┴─────┴─────┴─────┴─────┴─────┘
                                          ▲                 ▲
                                      has_billing       is_active
                                        (True)           (True)

How We Operate on Bitmasks:

1. Checking flags (Bitwise AND - &): To isolate and read a specific bit, we perform a bitwise AND between the integer column and the flag value.

   • For example, checking if a user has billing: status & 8.
   • If status = 9 (binary 00001001): 9 & 8 $\rightarrow$ 00001001 & 00001000 = 00001000 (decimal 8). Since 8 > 0, the flag is active.
   • If status = 5 (binary 00000101 - active and admin, no billing): 5 & 8 $\rightarrow$ 00000101 & 00001000 = 00000000 (decimal 0). The flag is inactive.

2. Setting flags (Bitwise OR - |): To turn a specific flag ON without affecting other flags, we use bitwise OR with the flag value.

   • For example, enabling billing: status | 8.
   • If status = 5 (active and admin): 5 | 8 $\rightarrow$ 13 (binary 00001101 $\rightarrow$ active, admin, AND has billing).

3. Toggling flags (Bitwise XOR - xor / ⊻): To flip a flag from 0 to 1 or 1 to 0, we use bitwise XOR.

   • For example, toggling billing: status ⊻ 8.
   • If status = 9 (active and billing): 9 ⊻ 8 $\rightarrow$ 1 (binary 00000001 $\rightarrow$ active, billing disabled).
   • If status = 1 (active, no billing): 1 ⊻ 8 $\rightarrow$ 9 (binary 00001001 $\rightarrow$ active, billing enabled).

All examples in this section are 100% real and executable directly against the standard Formula 1 dataset schema using the Driver model's number integer field.

Supported Operators

The operands are integers. An interval has no bits, so a bitwise operator with a DurationField column, a timestamp difference or any other interval on either side (F("time") & 1) raises QueryBuildError on every engine (#1581).

Real-World F1 Case Study: The "Clean vs. Dirty" Grid Side Analysis

In Formula 1, starting grid slots are staggered in two rows:

• Clean Side (Odd grid positions: 1, 3, 5, 7...): Positioned directly on the racing line where high rubber laydown provides maximum traction at the start.
• Dirty Side (Even grid positions: 2, 4, 6, 8...): Positioned off the racing line where dust, marbles, and debris significantly reduce launching grip.

By checking the lowest bit of the grid integer column (grid & 1), you can instantly classify clean/odd vs. dirty/even grid starts directly in the database.

For example, to find all podium finishers (top 3) who successfully fought their way up from a dirty starting slot (where (grid & 1) == 0):

query = M.Result.objects.values(
    "raceid__name",
    "driverid__surname",
    "grid",
    "position"
).filter(
    "position__@lte" => 3,     # Podium finisher
    (F("grid") & 1) == 0      # Started on the dirty (even) side
);
df = query |> DataFrame
@info (query |> inspect_query)[:sql_text]

Generated PostgreSQL SQL:

SELECT
    "Tb_1"."name" as raceid__name,
    "Tb_2"."surname" as driverid__surname,
    "Tb"."grid" as grid,
    "Tb"."position" as position
FROM "result" as "Tb"
 INNER JOIN "race" AS "Tb_1" ON "Tb"."raceid" = "Tb_1"."raceid"
 INNER JOIN "driver" AS "Tb_2" ON "Tb"."driverid" = "Tb_2"."driverid"
WHERE "Tb"."position" <= $1 AND (("Tb"."grid" & $2::bigint) = $3::bigint)
-- Parameters: $1 = 3 (Podium), $2 = 1 (Bitmask), $3 = 0 (Dirty side)

Dynamic Bitmasks & Scalar Left-Shift Operations

In advanced status and permission query models, you may store a bit index (an integer like $0, 1, 2...$) representing a flag's index, rather than storing a pre-computed bitmask.

PormG fully supports left-hand scalar bitwise shifts (e.g. 1 << F("field")) to dynamically generate one-hot bitmasks directly on the database server:

# Dynamically generate a bitmask from a stored bit index column:
query = M.Driver.objects.values(
    "surname",
    "index_mask" => 1 << F("number")
)
@info (query |> inspect_query)[:sql_text]

Generated PostgreSQL SQL:

SELECT
    "Tb"."surname" as surname,
    ($1::integer << "Tb"."number") as index_mask
FROM "drivers" as "Tb"
-- Parameters: $1 = 1

In Projections (values())

Mask or extract bits dynamically in a SELECT statement:

# Project a boolean column indicating if the driver has an odd racing number
query = M.Driver.objects.values(
    "surname",
    "number",
    "is_odd" => F("number") & 1
);
df = query |> DataFrame
@info (query |> inspect_query)[:sql_text]

Generated PostgreSQL SQL:

SELECT
    "Tb"."surname" as surname,
    "Tb"."number" as number,
    ("Tb"."number" & $1::bigint) as is_odd
FROM "drivers" as "Tb"
-- Parameters: $1 = 1

In Filters (filter())

Filter rows directly on server-side bitmasks:

# Find all drivers with odd racing numbers
query = M.Driver.objects.filter(
    (F("number") & 1) > 0
)
df = query |> DataFrame
@info (query |> inspect_query)[:sql_text]

Generated PostgreSQL SQL:

SELECT *
FROM "drivers" as "Tb"
WHERE (("Tb"."number" & $1::bigint) > $2::bigint)
-- Parameters: $1 = 1, $2 = 0

In Atomic Updates (update())

Atomically update bitmask columns:

# Toggle the lowest bit of a driver's racing number:
@info M.Driver.objects.
    filter("driverid" => 1).
    update("number" => F("number") ⊻ 1, show_query = :dict)[:sql_text]

Generated PostgreSQL SQL:

UPDATE "drivers" AS "Tb"
SET "number" = ("Tb"."number" # $2::bigint)
WHERE "Tb"."driverid" = $1
-- Parameters: $1 = 1 (driverid), $2 = 1 (xor mask)

# Ensure the racing number is odd by setting the lowest bit:
M.Driver.objects.
    filter("driverid" => 2).
    update("number" => F("number") | 1)

Generated PostgreSQL SQL:

UPDATE "drivers" AS "Tb"
SET "number" = ("Tb"."number" | $2::bigint)
WHERE "Tb"."driverid" = $1
-- Parameters: $1 = 2 (driverid), $2 = 1 (or mask)

Summary

Next Steps

• Q Objects — Complex AND/OR logic with Q() and Qor().
• Filters and Aggregates — Lookup operators and grouping.
• Writing: Updating Records — Full update documentation.