Functions and Dates

This page covers date extraction, SQL functions, mathematical transforms, and conditional expressions (Case/When).

Date Functions Overview

PormG provides date-related modifiers through the __@ suffix system. These work in both values() (to transform the selected value) and filter() (to create predicates on date components).

Available Date Transforms

Period number or period label?

@quarter and @quadrimester extract a number — 1–4 and 1–3 — so they answer "which quarter", independently of the year. That is what makes filter("date__@quarter" => 1) select Q1 of every season. It is the same shape as Django's __quarter lookup and SQL's EXTRACT(QUARTER FROM …) (cast ::integer on PostgreSQL, so it reads back as an integer on every engine; MySQL's own QUARTER(…) is an integer already).

@yyyy_q and @yyyy_quad build the year-qualified label ("1991-Q1"), the form you want as a values() grouping key when each year's periods must not be merged. They sit beside @yyyy_mm, which works the same way for months.

Both number transforms validate the comparison value: a quarter outside 1–4, or a value that is not a number at all, raises InvalidValueError instead of building SQL that silently matches nothing.

The labels work in every position — values(), filter() and order_by(), projected under an alias or not:

q = M.Race.objects
q.values("name", "q" => "date__@yyyy_q")
q.filter("date__@yyyy_q" => "1991-Q1")          # the races of the first quarter of 1991
q.order_by("-date__@yyyy_q")                    # newest label first

Date Component Selection

Select date parts as separate columns:

query = M.Race.objects
query.values("raceid", "date", "date__@year", "date__@month", "date__@day")
df = query |> DataFrame

#  Row │ raceid  date        date__year  date__month  date__day
# ─────┼────────────────────────────────────────────────────────
#    1 │      1  1991-03-10        1991            3         10
#    2 │      2  1991-03-24        1991            3         24

Date Component Filtering

Filter on extracted date components:

# All races in 2023
query = M.Race.objects.filter("date__@year" => 2023)

# Races in the second half of the year
query = M.Race.objects.filter("date__@month__@gte" => 6)

# Combine: Q1 races in 1991
query = M.Race.objects.filter("date__@year" => 1991, "date__@quarter" => 1)

Null checks and ranges after a transform

@isnull, @range and @nrange chain after a transform like any other lookup, as Django's date__year__isnull does:

# Races of the 1990s
query = M.Race.objects.filter("date__@year__@range" => [1990, 1999])
# renders:  EXTRACT(YEAR FROM "Tb"."date")::integer BETWEEN $1 AND $2

# Races with a sprint: `sprint_date` is NULL on every other race
query = M.Race.objects.filter("sprint_date__@year__@isnull" => false)
# renders:  EXTRACT(YEAR FROM "Tb"."sprint_date")::integer IS NOT NULL

The range operands are values of the transform: years for @year, "YYYY-MM" strings for @yyyy_mm. Each is checked for its shape the way a single value is, so "start_at__@hour__@range" => [1, 2.5] raises InvalidValueError. The ends are bounds, though, so they are not held to the part's range: [1, 25] builds (see the range rule below). A range is not rewritten onto the column the way a comparison on a DateField is (below), so it compares the transform itself.

A date part is NULL exactly when its date is, so "sprint_date__@year__@isnull" => false selects the same rows as "sprint_date__@isnull" => false. The year-qualified labels follow the same rule: "sprint_date__@yyyy_q" reads missing for a race without a sprint on every engine, never a partial "-Q".

Time of day (@hour, @minute, @second)

The time parts work on a DateTimeField and on a TimeField, in filter() and values() alike. They read back as integers on every engine. @second is the whole second: PostgreSQL truncates a fractional 45.6 to 45, as SQLite and MySQL do, instead of rounding it to 46.

# Races that started at 13:00-13:59 (`start_at` is a DateTimeField; see the time-zone note below)
M.Race.objects.filter("start_at__@hour" => 13)

# The same question asked of the TimeField: races scheduled on the half hour
M.Race.objects.filter("time__@minute" => 30)

# How many races started in each hour of the day
q = M.Race.objects
q.values("hour" => "start_at__@hour", "races" => Count("raceid"))
q.filter("start_at__@isnull" => false)
q.order_by("hour")

Each part validates its comparison value the same way @quarter does. An hour outside 0–23, a minute or second outside 0–59, a fraction, or a value that is not a number raises InvalidValueError, instead of building SQL that silently matches nothing.

Weeks (@week, @week_day, @iso_week_day, @iso_year)

The week parts use Django's numbering, and they return the same number on every engine:

The engines' own functions do not agree on these numbers. SQLite's %W is not the ISO week, nor is MySQL's default WEEK mode, and PostgreSQL's DOW counts from 0. PormG renders each part to the numbering in the table, not to any engine's default.

@week and @iso_year belong together. Near New Year a date can sit in a week of the neighbouring year: 2021-01-01 is in ISO week 53 of 2020, and 2024-12-30 is in week 1 of 2025. To group by week across seasons, group by both. @year alongside @week would split such a week in two.

# Races held on a Sunday (`date` is a DateField)
M.Race.objects.filter("date__@week_day" => 1)

# The same question in ISO numbering, where Sunday is 7
M.Race.objects.filter("date__@iso_week_day" => 7)

# How many races each ISO week of the 2021 season held
q = M.Race.objects
q.filter("date__@iso_year" => 2021)
q.values("week" => "date__@week", "races" => Count("raceid"))
q.order_by("week")

A week outside 1–53, a day outside 1–7, a fraction, or a value that is not a number raises InvalidValueError, the same as the other period transforms.

Which columns a transform reads

Each date part checks its column when the query is built, in values(), filter() and order_by() alike. A transform is shorthand for an Extract: "start_at__@hour" and Extract("start_at", "hour") build the same expression, so they follow the same rule and refuse with the same message.

Any other model field raises QueryBuildError naming the part, the column and its type. Over text or a number that SQL used to fail on PostgreSQL, while SQLite read whatever the column's text happened to hold, so a date stored in a CharField worked there and nowhere else. A DurationField reads only EPOCH. PostgreSQL would extract an hour from an interval, but SQLite reads its stored text as a clock, so the engines disagreed.

using PormG.Functions: Extract

M.Driver.objects.filter("surname__@month" => 3)     # QueryBuildError: `surname` is a CharField
M.Race.objects.values("h" => "date__@hour")         # QueryBuildError: a DateField has no time of day
M.Race.objects.values("h" => Extract("date", "hour"))  # the same QueryBuildError

The check covers columns PormG can name a field for: a model field, or one reached through a join ("raceid__date__@week"). An expression or a subquery has no field. For one of them, the part is checked against the duration the type walk names, by the same rows as a column. A duration expression reads only EPOCH, as a DurationField does: under any other part, a timestamp difference, interval arithmetic, a Sum or Avg of a duration, or a time minus a time on PostgreSQL raises QueryBuildError on every engine. A calendar part has no meaning over a duration, and a time-of-day part has no single one (see Subtracting two dates). Any other expression is passed through as written.

M.Result.objects.values("y" => Extract(F("fastestlaptime") * 2, "year"))   # QueryBuildError: a duration has no calendar date
M.Race.objects.values("q" => Extract(F("start_at") - F("date"), "quarter"))  # the same QueryBuildError
M.Race.objects.values("h" => Extract(F("start_at") - F("date"), "hour"))     # QueryBuildError: a duration has no time of day
M.Race.objects.values("s" => Extract(F("start_at") - F("date"), "epoch"))    # its seconds: builds on PostgreSQL and MySQL
M.Result.objects.values("y" => Extract(F("raceid__date") + F("fastestlaptime"), "year"))  # a timestamp: builds

A foreign key's value is the related row's key, so a part over it is checked against that key's field: on M.Result, "raceid__@year" raises QueryBuildError, because raceid holds the race's integer id. Read the race's date through the relation instead, with "raceid__date__@year". A foreign key into a table keyed by a date reads as that date. ToChar is checked by this row only for the "YYYY-MM" mask that @yyyy_mm uses, because to_char also formats numbers on PostgreSQL; any portable format that writes a date is refused over a time (#1336), and every format over a duration (#1416; see ToChar).

A date part's value has a range: @month and MONTH 1–12, @day and DAY 1–31, the clock and week parts as listed above, and the Extract parts DOW 0–6 and DOY 1–366. An exact filter is held to it, because a value outside it can only be a mistake: = and @in raise InvalidValueError instead of matching nothing. That covers a filter pair ("date__@month" => 13) and an alias of an Extract (values("m" => Extract("date", "month")); filter("m" => 13)). Every other lookup binds the number as given, because there a value outside the range is a bound: "date__@month__@lt" => 13 matches every race, and "start_at__@hour__@lte" => 24 reads "any hour". Django checks no range at all. PormG keeps the check where it catches a typo, and drops it where it would refuse a valid filter.

The shape is checked under every lookup. A Bool is refused rather than meaning 1 ("start_at__@hour" => true), and so are a fraction ("date__@month__@lt" => 6.5) and text that is not a number. A comparison written with F or Extract follows the same rule: F("date__@month") == 13 raises, F("date__@month") > 13 builds, and F("date__@month") > 6.5 raises. The range belongs to the part, not to what you compute from it: Extract("start_at", "hour") + 1 is an ordinary number, and Coalesce("date__@month", 0) can be filtered with its 0.

M.Race.objects.filter("date__@month" => 13)        # InvalidValueError: a month is 1 to 12
M.Race.objects.filter("date__@month__@lt" => 13)   # every race: 13 is a bound, not a month
M.Race.objects.filter("date__@month__@lt" => 6.5)  # InvalidValueError: no month is 6.5

Grouped Date Query

query = M.Race.objects
query.filter("date__@year" => 1991)
query.values(
    "date__@year",
    "date__@month",
    "date__@day",
    "rows" => Count("raceid")
)
query.order_by("date__day")
df = query |> DataFrame

Generated SQL (PostgreSQL):

SELECT EXTRACT(YEAR  FROM "race"."date")::integer AS date__year,
       EXTRACT(MONTH FROM "race"."date")::integer AS date__month,
       EXTRACT(DAY   FROM "race"."date")::integer AS date__day,
       COUNT("race"."raceid")                     AS rows
FROM "race"
WHERE ("race"."date" >= $1 AND "race"."date" < $2)   -- the @year filter is rewritten to a sargable range
GROUP BY 1, 2, 3
ORDER BY "date__day" ASC

Output:

16×4 DataFrame
 Row │ date__year  date__month  date__day  rows
     │ Int32?      Int32?       Int32?     Int64?
─────┼────────────────────────────────────────────
   1 │       1991            6          2       1
   2 │       1991           11          3       1
   3 │       1991            7          7       1
  ⋮  │     ⋮            ⋮           ⋮        ⋮
  14 │       1991            4         28       1
  15 │       1991            7         28       1
  16 │       1991            9         29       1
                                   10 rows omitted

Date Format Filtering

Match dates by formatted strings or Julia Date objects:

using Dates

# Match by year-month string
query = M.Race.objects.filter("date__@yyyy_mm" => "1991-10")

# Match by date string
query = M.Race.objects.filter("date__@date" => "1991-10-20")

# Match by Julia Date object
query = M.Race.objects.filter("date__@date" => Date(1991, 10, 20))

Index-friendly ranges on a DateField

Comparison suffixes work on these buckets too, and on a plain DateField column PormG rewrites them into a range directly on the column rather than comparing a formatted string:

# Every race from October 1991 onwards
query = M.Race.objects.filter("date__@yyyy_mm__@gte" => "1991-10")
# renders:  "Tb"."date" >= $1        with $1 = "1991-10-01"

# Every race up to and including December 1991
query = M.Race.objects.filter("date__@yyyy_mm__@lte" => "1991-12")
# renders:  "Tb"."date" < $1         with $1 = "1992-01-01"

The same applies to a date column reached through a join, at any depth and through any relation — a foreign key, a reverse relation, or a many-to-many:

# Results from races in October 1991 — the range lands on the joined table's column
query = M.Result.objects.filter("raceid__date__@yyyy_mm" => "1991-10")
# renders:  ("Tb_1"."date" >= $1 AND "Tb_1"."date" < $2)

# Drivers born from 1960 onwards
query = M.Result.objects.filter("driverid__dob__@year__@gte" => 1960)
# renders:  "Tb_1"."dob" >= $1       with $1 = "1960-01-01"

(The Tb_N alias is assigned per query in join order — a query joining both paths above would reach dob through "Tb_2".)

Because the column is not wrapped in a function call, an index on it applies and the query planner can estimate how many rows the filter selects — on a large table this is the difference between an index range scan and a full scan with a poisoned join plan.

Note that @lte includes the whole final bucket (it becomes < the following period's first day), while @lt excludes the named bucket entirely. The same holds for @year.

@year takes a whole year: an Integer, a whole-valued number, or a numeric string. A fraction or a Bool raises an InvalidValueError under every lookup, and so does a @yyyy_mm month that does not exist ("1991-13"). The range 1–9999 follows the date-part rule above: = and @in raise InvalidValueError for a year outside it, on any date or timestamp column. A comparison treats it as a bound. No date bound can express year 99999, so "date__@year__@gte" => 99999 skips the rewrite and compares the extracted year: it selects no race on any backend, and @lt selects every race.

String Functions

PormG.Functions provides string manipulation functions that work in values():

Length counts characters on every engine (MySQL renders CHAR_LENGTH, since its own LENGTH counts bytes). Lower and Upper fold every letter on MySQL and on PostgreSQL under a UTF-8 locale, and ASCII letters only on SQLite. LPad/RPad cut a longer value to len and repeat a longer fill as PostgreSQL does. With an empty literal fill they pad nothing and cut to len; on MySQL a fill taken from a column that is empty on a row gives "" instead (#1130).

using PormG.Functions: Concat, Value, Lower, Upper, Length

query = M.Driver.objects
query.values(
    "full_name" => Concat("forename", Value(" "), "surname"),
    "name_upper" => Upper("surname"),
    "name_length" => Length("surname")
)
query.limit(5)
df = query |> DataFrame

Generated SQL (PostgreSQL):

SELECT CONCAT("driver"."forename", $1::text, "driver"."surname")  AS full_name,
       UPPER("driver"."surname")                                  AS name_upper,
       LENGTH("driver"."surname")                                 AS name_length
FROM "driver"
LIMIT $2
-- parameters: [" ", 5]

Output:

5×3 DataFrame
 Row │ full_name          name_upper  name_length
     │ String?            String?     Int32?
─────┼────────────────────────────────────────────
   1 │ Lewis Hamilton     HAMILTON              8
   2 │ Nick Heidfeld      HEIDFELD              8
   3 │ Nico Rosberg       ROSBERG               7
   4 │ Fernando Alonso    ALONSO                6
   5 │ Heikki Kovalainen  KOVALAINEN           10

A NULL operand is skipped — read as an empty string — on every engine, as in Django, so a Concat is never NULL: a driver with no number gives "# " for Concat(Value("#"), "number", Value(" ")). PostgreSQL's CONCAT and MySQL's CONCAT_WS('', …) skip a NULL themselves; on SQLite each operand renders as COALESCE(operand, ''), joined with ||. To get NULL instead, say so with a Case, which is NULL when no branch matches:

using PormG.Functions: Case, When, Concat, Value

q = M.Driver.objects
q.values("driverref", "car" => Case(When("number__@isnull" => false, then = Concat(Value("#"), "number"))))

Text, integer and date operands read the same on every engine and pass. Any other operand is refused with QueryBuildError on every engine, because the query does not say which text it means: a boolean, a float, a decimal, a timestamp, an interval or a whole JSON document has a text only once you choose one, and each engine writes its own (the table below). This is Django's rule for mixed types (say the type you want), not a difference between engines (#1027, #1147). The refusal covers:

• a BooleanField, FloatField, DecimalField, DateTimeField, DurationField or JSONField column, joined paths included;
• a true, 1.5, Decimal, DateTime or Period literal;
• an expression of one of those types: a comparison or Q(...) condition, arithmetic, an extremum or window value, a Cast to a float or a decimal, Extract(x, "epoch") (the seconds as a decimal, #1457), Extract(x, "milliseconds") and JULIAN over a timestamp (#1552), Sum of a duration, and the functions PostgreSQL computes as numeric (Avg, Round, Mod, Sqrt, Exp, Ln, Power). Round over an integer is that integer (#1196), so it passes;
• a Case with no output_field that has one such branch: its value is a branch's on each row, so Case(When("grid" => 1, then = 1.5), default = 0) is a float (#1187). Arithmetic over such a Case is read by the same branches, so Concat over Case(…) + 1 is refused too (#1551). A Case whose branches are all text, integers or dates passes (inside arithmetic too), and so does one with output_field = "text", unless a branch is a boolean: that Case is refused on SQLite and MySQL by its own check (#1217).

A literal is refused when the Concat is built, and a column when the query is. A date, a time and a uuid pass. So does a DecimalField with decimal_places = 0: it holds whole numbers, as an integer column does (#1087). It stays whole through +, - and * with an integer or another zero-scale decimal, through a Case or Coalesce over such values, and through a CTE column that projects one (#1550). Divided, it is a decimal quotient, and refused like any decimal.

A cast to a zero-scale decimal is whole too: Cast(x, "numeric(p,0)"), numeric(p), a DecimalField(…, decimal_places = 0), or a numeric(p,0) output_field. PostgreSQL and MySQL round the value to a whole number and write the integer (15 for 14.6, 14 for 14.0), so Concat over one builds there. SQLite does not round such a cast: it keeps a float as it is, so a whole float writes 14.0. It writes 14 over an integer, so Concat(Value("#"), Cast("grid", "numeric(2,0)")) builds on every engine. Over any other value, such as Cast(Floor("points"), "numeric(10,0)"), Concat raises QueryBuildError on SQLite. Cast it to an integer instead, Cast(Cast(Floor("points"), "numeric(10,0)"), IntegerField()), which writes 14 on every engine. A fractional value cast to a scale is refused on SQLite before that, as a rounding it does not do (see A cast an engine computes differently).

Say which text you mean:

• cast it to text, Cast("points", "text"), which writes the engine's own text:

  Operand	PostgreSQL	SQLite	MySQL
  a float 25.0	25	25.0	25
  a numeric(10,2) decimal 3	3.00	3	3.00
  a timestamp	2009-03-29 06:00:00+00	2009-03-29T06:00:00.000+00:00	2009-03-29 06:00:00.000
  an interval	its IntervalStyle: PT25.021S, 1 day 02:00:00	00:00:25.021	00:00:25.021
  a whole JSON document	{"a": [1, 2]}	{"a":[1,2]}	{"a": [1, 2]}

  It is the same value written two ways, so PormG builds it on every engine and leaves the spelling to each, a quotient over a decimal (F("price") / 2) included: it is 7.5 on every engine (#1150). A boolean is the exception: SQLite and MySQL have no boolean and write 1, so a boolean's cast to text is refused on both;

• for a boolean, name its two texts with a Case;

• for a timestamp, name the format with ToChar: ToChar("start_at", "YYYY-MM-DD HH:MI:SS") gives 2009-03-29 06:00:00 on every engine;

• or fetch the value and format it in Julia, where you choose the digits.

Floor, Ceil and Abs over an integer, and Sum of integers, are integers on every engine (#1147), so Floor("grid") / 2 and Sum("resultid") / 2 divide as integers and pass. An integer here includes a bitwise operator or a shift over integers (F("grid") & 3, F("resultid") << 1, which has its left operand's type) and a Subquery projecting one (#1153). One integer quotient is a question of meaning rather than of text: a Sum over a number PormG cannot type (arithmetic with a JSON key's value, an Extract part it states no type for) may be a bigint or a numeric sum, which PostgreSQL computes as numeric and MySQL as a DECIMAL, and so keep the half. It is refused on PostgreSQL and MySQL (#1111), whatever the sum holds: PormG cannot tell, so it does not guess. Give the dividend a type (Cast(Sum(…), BigIntegerField()) / 2) or divide by a float to say which quotient you mean. A sum PormG types as a float or a decimal is that kind's quotient (#1157), and a sum of temporal arithmetic (Sum(F("fastestlaptime") * 2)) is an interval, not a number.

The check covers only types PormG can name: an operand it cannot type (an extremum over timestamp arithmetic, Greatest/Least over computed timestamps or intervals, a Case whose branches are such values) passes. A JSON key lookup ("payload__driver") passes too: PormG cannot know which type a key holds. A Subquery is classified by the one expression it projects (#1124): one over a float column or an Avg is refused as the float or the Avg is, and one over a text or integer column, or over Cast(Round(x), IntegerField()), passes. A CTE column is classified by the type its body expression means: an Avg column is a float (or a decimal, or an interval), as the Avg is, and a column that is an integer quotient PostgreSQL or MySQL computes as a decimal is refused as that quotient is (#1127).

using PormG.Functions: Case, When, Concat, Cast, Value

q = M.Result.objects
q.filter("raceid" => 18)
# A yes/no: name its two texts with a `Case`.
q.values("driverid__driverref", "points",
         "outcome" => Concat("driverid__driverref", Value(": "),
                             Case(When("points__@gt" => 0, then = Value("scored")), default = "no points")),
         # A number: the engine writes its own text, `10` on PostgreSQL and MySQL and `10.0` on SQLite.
         "label" => Concat("driverid__driverref", Value("-"), Cast("points", "text")))
df = q |> DataFrame
# Or choose the digits yourself, in Julia.
df.rounded = df.driverid__driverref .* "-" .* string.(round.(df.points; digits = 1))

Concat takes its operands either variadically or as a single vector — the two spellings build the same expression and render the same SQL. Each example below starts from a fresh handle, because values() replaces the projection rather than adding to it:

# Vector form, equivalent to the variadic call above
q = M.Driver.objects
q.values("full_name" => Concat(["forename", Value(" "), "surname"]))

# A vector of plain column names works too, which is what a parsed request usually gives you
cols = split("cols=forename,surname", "=")[2]
q2 = M.Driver.objects
q2.values("full_name" => Concat(collect(split(cols, ","))))

Padding: LPad and RPad

LPad(x, len, fill = " ") pads a string on the left until it is len characters long, and RPad pads on the right. Both work in values(), in update(), and anywhere else an expression goes. The common case is a fixed-width code built from a number: zero-filling a car number into three characters. The column must be text, so convert a number with Cast first:

using PormG.Functions: LPad, RPad, Cast

q = M.Driver.objects
q.filter("driverid__@in" => [1, 4, 20, 830])
q.values("driverref", "number",
         "car"  => LPad(Cast("number", "text"), 3, "0"),
         "code" => RPad("code", 5, "."))
q.order_by("driverid")
df = q |> DataFrame

Generated SQL (PostgreSQL):

SELECT "Tb"."driverref" AS "driverref",
       "Tb"."number"    AS "number",
       LPAD(("Tb"."number")::text, ($1::bigint)::integer, $2::text) AS "car",
       RPAD("Tb"."code", ($3::bigint)::integer, $4::text)           AS "code"
FROM "driver" AS "Tb"
WHERE "Tb"."driverid" = ANY($5)
ORDER BY "Tb"."driverid" ASC NULLS LAST
-- parameters: [3, "0", 5, ".", [1, 4, 20, 830]]

Output:

4×4 DataFrame
 Row │ driverref       number  car      code
     │ String?         Int32?  String?  String?
─────┼──────────────────────────────────────────
   1 │ hamilton            44  044      HAM..
   2 │ alonso              14  014      ALO..
   3 │ vettel               5  005      VET..
   4 │ max_verstappen      33  033      VER..

The same expression in update() writes the padded text into a column in one statement. You don't need to fetch the rows, pad them in Julia and bulk_update them:

q = M.Driver.objects
q.filter("driverid" => 1)
q.update("code" => LPad(Cast("number", "text"), 3, "0"))   # "HAM" becomes "044"

Every engine gives the same value, but for a fill column that is empty on a row, which gives "" on MySQL. PostgreSQL and MySQL render their own LPAD/RPAD. SQLite has neither, so PormG registers pormg_lpad/pormg_rpad on every SQLite connection it opens, and SQLite's show_query output names them. Those functions follow PostgreSQL's rules:

• A string longer than len is cut to its first len characters, by RPad too.
• A fill of several characters repeats and is cut where the length is reached.
• An empty fill pads nothing.
• A NULL string is NULL.
• Lengths count characters, not bytes.

These are refused when the query is built, on every engine:

• A value that is not text raises QueryBuildError, because PostgreSQL has no lpad over one: a number, a date, a time, a timestamp, an interval, a boolean, a uuid or a whole JSON document. A JSON key lookup ("payload__driver") is text and passes. Convert the rest to text first: Cast(x, "text") for an integer, a date, a time or a uuid, ToChar for a timestamp, a Case for a boolean. A float, a decimal, an interval or a document reads differently on each engine, so fetch it and format it in Julia.
• A fill that is not text raises QueryBuildError too: a number, a date or a boolean, as a literal or as a column. Write the literal as a string: "0", not 0.
• A len below 0 or above 268435454 raises InvalidValueError. 268435454 is PostgreSQL's own limit in a UTF-8 database, which refuses anything longer as "requested length too large".

Part of a string: Substr

Substr(x, pos, len = nothing) returns len characters of a string, starting at position pos counted from 1. With no len, it returns everything from pos to the end. It works in values(), filter(), update() and join conditions, like the other string functions:

using PormG.Functions: Substr

q = M.Driver.objects
q.filter("driverid__@in" => [1, 4, 20, 830])
q.values("surname", "code",
         "first3" => Substr("surname", 1, 3),
         "rest"   => Substr("surname", 4),
         "past"   => Substr("surname", 50, 2))
q.order_by("driverid")
df = q |> DataFrame

Generated SQL (PostgreSQL):

SELECT "Tb"."surname" AS "surname",
       "Tb"."code"    AS "code",
       SUBSTR("Tb"."surname", ($1::bigint)::integer, ($2::bigint)::integer) AS "first3",
       SUBSTR("Tb"."surname", ($3::bigint)::integer)                        AS "rest",
       SUBSTR("Tb"."surname", ($4::bigint)::integer, ($5::bigint)::integer) AS "past"
FROM "driver" AS "Tb"
WHERE "Tb"."driverid" = ANY($6)
ORDER BY "Tb"."driverid" ASC NULLS LAST
-- parameters: [1, 3, 4, 50, 2, [1, 4, 20, 830]]

Output:

4×5 DataFrame
 Row │ surname     code    first3  rest     past
     │ String      String  String  String   String
─────┼─────────────────────────────────────────────
   1 │ Hamilton    HAM     Ham     ilton
   2 │ Alonso      ALO     Alo     nso
   3 │ Vettel      VET     Vet     tel
   4 │ Verstappen  VER     Ver     stappen

A pos past the end gives "", and a NULL string gives NULL. In a filter, Substr compares with a literal, a column (F(...)), another function or, in a cjoin_on condition, a joined column (Joined(...)). This finds the drivers whose code is the first three letters of their surname:

using PormG.Functions: Substr, Upper

M.Driver.objects.filter(Upper(Substr("surname", 1, 3)) == F("code")).count()

SQLite's UPPER changes only ASCII letters, so a surname that starts with an accented letter can compare differently there than on PostgreSQL. In update(), Substr derives a column from another in one statement:

M.Driver.objects.
    filter("code__@isnull" => true).
    update("code" => Upper(Substr("surname", 1, 3)))

A comparison can take a function on either side (#1514). These are the drivers whose forename and surname start with the same letter:

M.Driver.objects.filter(Substr("forename", 1, 1) == Substr("surname", 1, 1)).count()

The filter pair spells the same comparison through a projected name, since its right-hand side can be a function too:

q = M.Driver.objects
q.values("forename", "surname", "initial" => Substr("forename", 1, 1))
q.filter("initial" => Substr("surname", 1, 1))

PostgreSQL, SQLite and MySQL give the same value only for a position from 1 and a length from 0, over text. Everything else is refused when the expression or the query is built, on every engine:

• A pos below 1 or a negative len raises InvalidValueError. Outside that range the engines disagree. A negative position counts from the end on SQLite and MySQL and gives "" on PostgreSQL. A negative length raises on PostgreSQL, while SQLite returns the characters before pos. A pos or len above 2147483647, a Bool, a float or a column raises InvalidValueError too.
• A value that is not text raises QueryBuildError: a number, a date, a time, a timestamp, an interval, a boolean, a uuid or a whole JSON document. A JSON key lookup ("payload__driver") is text and passes. Convert the rest to text first, as for LPad: Cast(x, "text") for an integer, a date, a time or a uuid, ToChar for a timestamp, a Case for a boolean.

Mathematical Functions

PormG supports math through explicit function calls:

using PormG.Functions: Power, Round, Value, Abs

query = M.Driver.objects
query.values(
    "driverid",
    "squared" => Power("driverid", 2),
    "rounded" => Round(Value(10.556)),        # 11 on every engine
    "abs_val" => Abs("number")
)
query.filter("driverid" => 1)
df = query |> DataFrame

Over an integer — a column, an integer literal, a count, a ranking window, a Case of integers, an integer date part, or a CTE column holding an integer — Abs, Floor and Ceil return an integer on every engine (its width follows the engine, as an integer column's does), so Abs("number") reads an integer and Floor("grid") / 2 divides as integers (#1147). Over a float or a decimal — a CTE column computed by Avg included — PostgreSQL computes them as numeric and SQLite as its own number type, and the value reads back as the operand's type on every engine: a Float64 over a float, a Decimal over a decimal (#1121). The floor of an average of 7.5 is 7.0 on every engine; see The type of a computed number.

On MySQL (#1130) each is MySQL's own function, and over an integer it keeps it. Over a decimal MySQL computes Sqrt, Exp, Ln and Power as a double and Floor/Ceil as a BIGINT; each reads back as the Decimal it means, through 15 significant digits for the double ones, as on SQLite.

A value with no answer is an engine's own: Sqrt of a negative number, Ln of 0 and Mod(x, 0) raise on PostgreSQL and read as missing on SQLite and MySQL. An Exp or Power too large for a double raises on PostgreSQL and MySQL and reads as Inf on SQLite.

A date, a timestamp, a time or a UUID under any of these functions, or under Sum or Avg, raises QueryBuildError on every engine when the query is built (#1387): none of them is a number. A column, a path across a relation ("raceid__date") and a date expression (F("date") + Day(1), Max("date")) are all refused. PostgreSQL raised at the server (round(date) does not exist), SQLite computed on the stored text (Round("date") read the year's digits), and MySQL on the number the date's digits spell. Name the number you mean, and apply the function to that:

• a calendar part of a date or a timestamp: Extract("date", "year"), or the transform "date__@year";
• a clock part of a timestamp or a time: Extract("time", "hour");
• a day count, the difference of two dates: on Result, F("raceid__date") - F("driverid__dob") is an integer on every engine;
• the seconds, Extract(x, "epoch"): since 1970 for a date or a timestamp on PostgreSQL and MySQL, since midnight for a time on PostgreSQL. SQLite has no EPOCH part, and MySQL none for a time.

A UUID holds no number, so nothing is named for one. A JSON key lookup keeps its own rules (below), and so does a boolean.

Text under any of these functions, Sum and Avg included, raises QueryBuildError on every engine when the query is built (#1293): a CharField column, a Concat, an Upper, a "date__@yyyy_mm" label or a string literal. PostgreSQL rejects text that is no number, or computes the number the text spells, and SQLite reads it as 0. If the text holds numbers, say so with a cast and apply the function to that: Floor(Cast("positiontext", FloatField())) on Result.

Rounding to decimal places

Round(x, n) uses each engine's own ROUND, as Django's Round does. PostgreSQL renders ROUND(x::numeric, n), which rounds the value's exact decimal form. SQLite renders ROUND(x, n) over the stored double. Either way the result reads back as its operand's type on every engine: a DecimalField keeps its Decimal, and a float is a Float64 (#1121). Over an integer the places add nothing, so Round("grid") and Round("grid", 2) are that integer on every engine, and Round("grid") / 2 divides as integers, as Floor("grid") / 2 does (#1196).

MySQL rounds a decimal half away from zero but a double half to even (its ROUND(2.5E0) is 2), so on MySQL PormG rounds a float as ROUND(CAST(x AS DECIMAL(65,30)), n): half away from zero, the rule Round means, read back as a Float64. The cast takes the double's shortest digits where PostgreSQL reads a double at 15 significant digits, so a double that needs more can round the other way at a tie: Round(815.6949999999999, 2) is 815.69 on MySQL. The cast also clamps a float of 1e35 or more to the largest value the type holds.

The engines agree except at a decimal tie whose double sits just below it. Measured on PostgreSQL 16.15, SQLite 3.45.1 and MySQL 8.4:

PostgreSQL's is the exact answer. On SQLite, which PormG supports for development and tests, the last digit of such a tie can differ, so a test that pins a rounded tie value is engine-specific (#1061). Round(x), to a whole number, gives the same value on every engine. Rounding states the rules this follows: a value is rounded by the engine, only where the query asks, and PormG refuses where an engine would not honor a declared scale.

Two cases raise when the query is built:

• Text (a text column, a string, and a JSON value when n is not 0) raises QueryBuildError on every engine, Round(x) included (#1293): text is not a number, so say it holds one. Cast it first: Round(Cast(x, FloatField()), n).
• A negative n rounds to tens, hundreds, … on PostgreSQL and MySQL (Round(125, -1) is 130). SQLite's ROUND has no negative precision (it reads one as 0), so on SQLite it raises BackendCapabilityError (see Backend Capabilities).

using PormG.Functions: Round
# Race 2, the 2009 Malaysian GP, was stopped early and scored half points.
query = M.Result.objects
query.filter("raceid" => 2)
query.values("resultid", "points_1dp" => Round("points", 1))
df = query |> DataFrame

Conditional Functions

The operands of Coalesce, NullIf, Greatest and Least (and of Power and Mod above) are read by their type. A string is a column path, and the path can end in a transform: Coalesce("fp1_date", "start_at__@date") falls back to the date of the race start. That holds wherever the function sits. It can be projected in values(...), on a filter's right-hand side (also inside Q/Qor and a When condition), in a Case/When branch, in F arithmetic, in a window's partition_by, as a Lag/Lead default, or as an update(...) value:

using PormG.Functions: Coalesce

# Races whose date equals the first-practice date or, when there is none, the date of the start
M.Race.objects.filter("date" => Coalesce("fp1_date", "start_at__@date"))

A CTE column is the exception. Outside a projection, a function names it with the CTE("name", "column") handle; the "name__column" string works only inside values(...). A transform on a CTE column is not available in these positions with either spelling.

A number (of any integer width), a Bool, a Date, a DateTime, a ZonedDateTime or a Time is a literal that PormG binds as a parameter. A string literal needs Value(...): NullIf("code", "") would read "" as a column name. Any other value raises QueryBuildError when the expression is built.

Coalesce, Greatest and Least take two or more arguments. With fewer, they raise QueryBuildError when the expression is built: one argument is the argument itself, so write the column with F("points") directly.

On SQLite a date or time literal binds as the same text its column stores (Date(2021, 3, 28) is "2021-03-28"), and an integer of any width binds as a 64-bit integer, so a comparison with a DateField column picks the same rows as on PostgreSQL. The result reads back as the column does on every engine (#824) when every argument is of one type: Coalesce("date", Date(2021, 3, 28)) and Greatest("date", "fp1_date") give a Date, and NullIf takes its first argument's type. A Subquery(...) argument counts as its one column (#888). When the argument types differ (two temporal types, a temporal type and text, a number beside text, a temporal type or a UUID, or a UUID beside a temporal type), PormG does not guess: the call is refused on every engine (see Mixed date, time, text and number operands).

Coalesce — First Non-Null Value

using PormG.Functions: Coalesce

query = M.Driver.objects
query.values(
    "display_name" => Coalesce("code", "surname")
)

NullIf — Return NULL If Equal

using PormG.Functions: NullIf, Value

# Return NULL if code is an empty string. `Value` makes "" a literal, not a column.
query = M.Driver.objects
query.values(
    "clean_code" => NullIf("code", Value(""))
)

Greatest / Least — Max/Min of Values

using PormG.Functions: Greatest, Least

query = M.Result.objects
query.values(
    "adjusted_points" => Greatest("points", 0),
    "capped_points"   => Least("points", 25)
)

# A date literal works the same way: the later of the race date and 1 January 2021.
using Dates
query = M.Race.objects
query.filter("year" => 2020)
query.values("name", "not_before_2021" => Greatest("date", Date(2021, 1, 1)))

Greatest and Least skip a NULL argument on every engine. The result is NULL only when every argument is NULL. The 2018 Hungarian Grand Prix has a race date and no practice dates:

query = M.Race.objects
query.filter("raceid" => 1000)
query.values("latest" => Greatest("date", "fp1_date"), "none" => Greatest("fp1_date", "fp2_date"))
query.list(:dict)
# [Dict(:latest => Date("2018-07-29"), :none => missing)]

PostgreSQL's GREATEST and LEAST behave this way natively. SQLite has neither function, and its scalar MAX(a, b) returns NULL when any argument is, so on SQLite PormG renders one COALESCE per rotation of the arguments: Greatest(a, b) becomes MAX(COALESCE(a, b), COALESCE(b, a)). MySQL has its own GREATEST and LEAST, which also return NULL when any argument is, so it gets the same rotations: GREATEST(COALESCE(a, b), COALESCE(b, a)). A literal argument binds once per place it appears. The SQL grows with the square of the argument count, so with n arguments each one is rendered n times; a Subquery argument runs once per rotation.

A single argument is refused (see the start of this section). On SQLite, MAX(x) and MIN(x) with one argument are the aggregates, so a one-argument Greatest used to collapse the result to one row.

Mixed date, time, text and number operands

Coalesce, Greatest, Least, NullIf, a Case with no output_field, and a Lag/Lead with a default all return one of their operands' own values. Their operands must therefore be of one temporal type. A date, a timestamp without a time zone, a timestamp with one, a time and an interval are five different types here, and text is a sixth. Two different types, or a temporal type beside text, are refused with QueryBuildError when the query is built, on every engine (#1200):

using Dates, TimeZones
using PormG.Functions: Coalesce

zdt = ZonedDateTime(DateTime(2021, 1, 1, 0, 30), tz"UTC")

# ✗ a date and a timestamp with time zone: refused
M.Race.objects.values("first_day" => Coalesce("fp1_date", zdt))

# ✓ convert the literal in Julia…
M.Race.objects.values("first_day" => Coalesce("fp1_date", Date(zdt)))
# ✓ …or say the type with `output_field`
M.Race.objects.values("first_day" => Coalesce("fp1_date", zdt; output_field = "date"))

The query does not say which type it means, and each engine would pick its own. With a DateField beside a zoned literal, PostgreSQL resolves a timestamptz and returns a ZonedDateTime on every row, while SQLite returns each row's own text: a date on one row, a UTC timestamp on another. A text column beside a timestamp fails at the PostgreSQL server and builds on SQLite. Django refuses the same expressions for the same reason (Expression contains mixed types … You must set output_field).

The ways to say the type:

• convert the literal in Julia: Date(zdt), DateTime(zdt, UTC), or ZonedDateTime(dt, tz"UTC") for a DateTime beside a DateTimeField. A DateTimeField is a timestamp with a time zone unless declared type = "TIMESTAMP", so a plain DateTime beside one is refused;
• an output_field on Coalesce, Greatest, Least or Case, for a date beside a timestamp. It casts the function's result, so the operands inside must still have a common type, which a date and a timestamp do on PostgreSQL. "date" works on every engine; a timestamp type cannot be cast on SQLite (BackendCapabilityError, see output_field below). NullIf, Lag and Lead take no output_field: cast one operand instead;
• beside text, cast the other operand to text: Coalesce("name", Cast(Value(zdt), "text")). Each engine writes the timestamp's text its own way (see the Concat section). An output_field does not help here: PostgreSQL has no common type for text and a timestamp inside the function;
• a time, an interval and a date among themselves have no common type at all: make them one, in Julia or by casting each to text.

An operand whose type PormG cannot name (a JSON key lookup) is not checked by this rule, and the function then has no type PormG can name either: used in arithmetic or an ordering beside a date or a duration, it is refused on SQLite and MySQL (A value PormG cannot type beside a date or a duration). A number beside another number is not this rule's concern: Coalesce("grid", 0.5) is a float on every engine.

Text beside a number is refused the same way, on every engine (#1327): Coalesce("positiontext", 0), Greatest("positiontext", "grid"), Case(When("grid" => 1, then = 1), default = "x"). The query does not say whether the text is compared as text or the number as a number, and the engines disagree: SQLite places any text above any number, MySQL compares the two as strings (GREATEST('10', 9) is '9'), and PostgreSQL fails at the server. Cast the operand that differs, so both have one type:

using PormG.Functions: Coalesce, Cast, Value

M.Result.objects.values("pos" => Coalesce("positiontext", Cast(Value(0), "text")))   # text
M.Result.objects.values("pos" => Coalesce(Cast("positiontext", "integer"), 0))       # a number

An output_field does not lift this refusal. It casts the result after the engine has already met text and a number inside the function, so PostgreSQL still raises and MySQL still compares as strings.

A number beside a date, a time, an interval or a UUID, and a UUID beside a date, a time or an interval, is refused the same way (#1321). PostgreSQL has no common type for them, and SQLite returns whichever operand is not NULL: update("grid" => Coalesce("grid", "racedate")) stored the date's text in the integer column on the rows where grid was NULL. Say which value you mean, for example the date's year, Coalesce("grid", Extract("racedate", "year")). An output_field does not lift this one either.

A boolean or a JSON document beside another type is refused the same way (#1386). A boolean beside a number does not say whether it means the flag or a count: SQLite and MySQL store the flag as 0/1 and answered a number, and PostgreSQL fails at the server. Write the number the flag means with a Case, which builds on every engine. Beside text, write the text the same way:

using PormG.Functions: Case, When

# ✗ a boolean and a number: refused
M.New_join_position.objects.values("v" => Coalesce("boolean_field", "result"))
# ✓ the count the flag means
M.New_join_position.objects.values("v" => Case(When("boolean_field" => true, then = 1), default = 0))

A whole JSON document (a JSONField column) beside a number, text, a boolean or a date has no common type either. A JSON key lookup ("payload__driver") is not checked: its type is the data's.

One pair is not checked. A UUID beside text builds: SQLite and MySQL hold a UUID as its text, so Coalesce("token", Cast("name", "uuid")) is that text there, and PostgreSQL raises at the server for a text column beside a UUID.

Cast — Type Conversion

using PormG.Functions: Cast, Round
using PormG.Models: IntegerField

query = M.Result.objects
query.values(
    "points_int" => Cast(Round("points"), IntegerField()),   # a field object: each engine's own spelling
    "points_num" => Cast("points", "numeric")                # or a type string
)

A field object is the preferred target, as in Django: PormG renders it in each engine's spelling (BinaryField() is bytea on PostgreSQL and BLOB on SQLite; MySQL has no binary cast target that reads back as bytes, so there it raises UnsupportedConnectionError). A type string is accepted when it has this shape:

• a single type name — "integer", "bigint", "text", "timestamptz", or your own type such as an enum — or one of "double precision", "character varying", "bit varying", "timestamp with time zone", "timestamp without time zone", "time with time zone", "time without time zone" (any case);
• optionally followed by a size, (n) or (n, m): "varchar(20)", "numeric(10,2)", "timestamp(3) with time zone";
• on PostgreSQL only, optionally followed by array brackets: "integer[]". SQLite and MySQL have no array types and raise BackendCapabilityError. A one-dimensional array of an element an ArrayField can hold (varchar, text, integer, bigint, float, numeric, boolean, date, timestamp, timestamptz, uuid) reads back as the Vector that ArrayField reads as, on both PostgreSQL drivers: Cast("tyre_compounds", "varchar[]") is ["SOFT", "MEDIUM"]. Any other array (smallint[], real[], integer[][]) arrives as the driver returns it.

On MySQL a name with no MySQL CAST target that holds its meaning (interval, bytea, a type of your own) raises UnsupportedConnectionError before any SQL (MySQL Guide).

SQLite has no date or time types either. There, a cast to "date" (or DateField()) renders date(x), which returns the YYYY-MM-DD text a DateField stores and cuts a timestamp to its date, as PostgreSQL's ::date does. Every engine reads the result back as a Date. Cast a date or timestamp column: on other input SQLite does not raise where PostgreSQL does. Text that is no date gives NULL, an impossible date rolls over ('2020-02-30' is 1 March), and a number is read as a Julian day (0 is in 4714 BC). Every other time target raises BackendCapabilityError on SQLite: timestamp, timestamptz, time, interval and their spellings, and DateTimeField(), TimeField(), DurationField(). A plain SQLite CAST to one of them returns a number (2020 for '2020-03-29 10:11:12'), not a different spelling of the value. Project the column itself instead. The same holds for "bytea" (#1194): SQLite has no type of that name, and CAST(… AS BYTEA) turns 'abc' into 0. Cast to BinaryField() (or "blob") instead, which renders CAST(… AS BLOB) on SQLite and ::bytea on PostgreSQL. MySQL has no binary cast target that reads back as bytes, so both raise UnsupportedConnectionError there.

On PostgreSQL a cast to a time, timestamp, interval or binary type is typed as that type (#1194), as a column of it is: it reads back as a Time, DateTime/ZonedDateTime, Dates.CompoundPeriod or bytes, an equality value on its alias is checked as one, a pattern lookup on the alias follows the column's rule (a time reads as its text; the others are refused), and Concat over a timestamp or interval cast is refused (name its text with ToChar or Cast(…, "text")).

using PormG.Functions: Cast

# The race date as a Date, on every engine
M.Race.objects.filter("year" => 2020).values("raceid", "day" => Cast("date", "date"))

Any other string raises InvalidValueError when the expression is built, on every engine. A type name is a keyword in the SQL and cannot be a bind parameter, so PormG only writes a spelling it has parsed, never the text it was given. That also refuses a few spellings PostgreSQL itself accepts: a schema-qualified or quoted name (public.mood, "Mood"), interval year to month, a negative scale, and a COLLATE clause. The same rules apply to the output_field= string of Case, Coalesce, Concat, Greatest and Least.

A cast an engine computes differently

A cast to text, to text of a declared length, to an integer, to a scaled numeric(p, s), to a single-precision float or to a date goes through each engine's own conversion. PormG checks each engine against what the cast means, never against the other engine (#1147). The engine that departs raises QueryBuildError when the query is built, and the others build the query as written. For the scale and integer rows, Rounding shows the explicit spelling that builds on every engine. Measured on PostgreSQL 16, SQLite 3.45 and MySQL 8.4:

The text of a float, a decimal, a timestamp or a document is the same value written two ways, so it is documented, not refused: the Concat section above lists each engine's text. A quotient is the number it means on every engine: over a decimal, or over a value that is one of several where one is a fraction, SQLite would divide its integer rows as integers, so PormG casts the dividend to REAL there (#1150, see Field Expressions). Cast(F("price") / 2, "text") therefore builds on every engine, and Cast(F("price") / 2, IntegerField()) is the fraction-to-integer row above. A cast refused only on SQLite cannot run on the development engine, so its test belongs on PostgreSQL or MySQL. That is the intended trade: the engine that cannot give the meaning says so, rather than answering differently.

What each row covers:

• text: CharField(), TextField(), "text", "varchar", and the output_field of Coalesce, Greatest, Least and Case, which renders the same cast;
• a length: "varchar(n)", "character varying(n)", "char(n)", "character(n)", PostgreSQL's "bpchar(n)" and "nchar(n)", an unsized "char", "character" or "nchar", which is one character ('H'), CharField(max_length = n), SlugField and URLField, and the same output_field (#1252, #1438). PormG maps bpchar and nchar to no MySQL CAST target, so they are PostgreSQL's spellings: MySQL refuses them with UnsupportedConnectionError. Every operand is refused on SQLite, which enforces no declared length, so the text would come back whole where PostgreSQL and MySQL cut it. For the text on every engine, cast to text of no declared length ("text" or CharField()) and cut the fetched value in Julia (first(s, n)). A Concat's output_field renders no cast, so a length there cuts nothing and builds everywhere;
• an integer: IntegerField(), BigIntegerField(), "integer", "bigint", "int8", … A boolean casts to 1/0 on every engine, and a whole number (Round(x), Floor(x), Ceil(x), Mod of whole numbers, +, -, * of them, an integer cast to a numeric) has nothing to round; both pass on every engine (#1167). An interval (a DurationField, its Sum or Avg, a duration literal, temporal arithmetic) is refused on SQLite and MySQL, here and under any other number type (#1259). For a number from a duration, fetch it and take the number in Julia. So is a date, a timestamp or a time (a column, Max of one, a cast to date, date arithmetic, a literal), whose number is a part: "date__@year" or Extract("date", "year"), "time__@hour" (#1260). A part and a day count (F("date") - F("date")) are numbers and pass. A boolean is a number only as an integer: its cast to a float or a numeric is refused on SQLite and MySQL (#1319). Cast it to the integer first, Cast(Cast(x, IntegerField()), "float8"), or state the values with a Case;
• a date: DateField() and "date", and the same output_field. A duration (a DurationField, its Sum, interval arithmetic, a duration literal) and a time of day (a TimeField, Max of one, a Time literal) have no calendar date, so their cast is refused on SQLite and MySQL (#1325). A duration places a date only from another one: F("date") + F("fastestlaptime"). Nor has a number (an integer, a float or a decimal: a column, a literal, Floor, Sum, arithmetic): its cast is refused on SQLite, which reads it as a Julian day (date(5) is -4713-11-29), and on MySQL, which reads its digits as a date (#1417). PostgreSQL has no such cast. A number of days places a date from another one: F("date") + Day(1). A date and a timestamp cast to a date build on every engine;
• a scale: numeric(p, s), decimal(p, s), dec(p, s), and numeric(p), whose scale is 0 (#1040, #1078). An operand with more than s places is a float column, a float literal with more than s places (Value(1.5) at scale 2 passes, #1050), a function PostgreSQL computes as numeric (Round(x, d) with d above s included, over a non-integer x), a decimal with more places than s or of unknown scale, and text, which PostgreSQL and MySQL parse and round. A JSON value counts as text. An integer, a whole number, a DecimalField with at most s places and a function whose value is one of them (Max, Min, Abs, Coalesce, Greatest, Least, NullIf) pass. So does a Case with no output_field whose every branch passes (#1187): then = 1.5 at scale 2 passes, a FloatField branch at scale 0 does not. A CTE column keeps the scale its body declares, a DecimalField column or a cast to numeric(p, s), so it passes the same way; a decimal the body computes (Avg("price")) has no declared scale;
• single precision: "real", "float4" and "float(p)" for p up to 24, which PostgreSQL resolves to real (#1215), and the same output_field. Every operand is refused on SQLite, an integer column included, because the value PostgreSQL returns is a Float32 whatever it rounded, and a Float32 holds an integer exactly only up to 2^24 (16 777 216): unlike a scaled numeric, which lets a whole operand through (#1040), single precision changes an integer's value (#1258). For a float on every engine, cast to a double: "float8", "float(25)" to "float(53)", "double precision" or FloatField(), a Float64 on every engine;
• an overflow: only a literal is certain to overflow, so only a literal is refused when the query is built (#1087). A column or a computed value overflows on the rows that hold a large value and not on the others. PostgreSQL raises "numeric field overflow" on such a row when the query runs. SQLite returns the value as it is, and MySQL's DECIMAL(p, s) clamps it to the largest value the type holds with only a warning (99 for numeric(2,0)). So PormG raises the same error as it reads the row (#1110): on MySQL it computes the cast at DECIMAL(65, s), which rounds to the same scale, and applies p as the row is read. Every engine raises StatementError with e.sqlstate == "22003", whose message names the projection and the declared type but never the value. The literal of a Coalesce, Greatest, Least or Case with an output_field is not refused when the query is built, because it is one candidate value among the operands. A row whose value overflows raises like any other. Two limits:
  • a cast used only in a filter, an ORDER BY or a GROUP BY is never read back, and one composed into arithmetic or an aggregate (Cast(x, "numeric(2,0)") + 1, Max(Cast(…))) reads as the computed number it means, so neither is checked: SQLite and MySQL use the value as it is there;
  • SQLite checks a declaration of at most 15 digits, the width it stores exactly.

A Case with no output_field is read branch by branch, each then and its default as an operand of its own, since its value on a row is one of them (#1151, #1187):

• to an integer, a float branch (then = 1.5, a FloatField column) or a DecimalField branch with places is a fraction, so the cast is refused on SQLite; a DecimalField with decimal_places = 0 is whole and passes;
• to numeric(p, s), each branch is held to the scale: a FloatField branch at numeric(10,0) is refused on SQLite, a 1.5 branch at numeric(10,2) passes on every engine (#1050).

A Case with an output_field is that cast, taken on whichever branch a row matches, so its branches are held to it the same way (#1217): Case(When("grid" => 1, then = F("points")), default = 0; output_field = "numeric(10,1)") is refused on SQLite, and then = Round("points", 1) passes on every engine. A Cast over such a Case reads its declared scale, so numeric(10,1) under a numeric(10,2) cast passes. An operand PormG cannot type (a JSON key lookup) passes every row. A Subquery is classified by the expression it projects (#1124).

To get an integer on every engine, say how to round first. Round(x), Floor(x) and Ceil(x) give the same whole number on every engine for every stored value measured (PostgreSQL's round is the numeric one, and MySQL rounds a float as a DECIMAL, half away from zero, as SQLite's is). Over an integer, Floor, Ceil and Abs are that integer on every engine, and so is integer arithmetic (#1147): Floor(F("grid") + 1) / 2 divides as integers on every engine (7). Round(x, 2) keeps a fraction, so a cast to an integer over it is refused on SQLite. One caveat: PostgreSQL turns a float into numeric at 15 significant digits before it rounds, so a computed value a hair below a half (2.4999999999999996) can still round up there and down on SQLite and MySQL. The same conversion reaches a float cast to a scaled numeric: a value with more than 15 significant digits (12345678901234.56 becomes 12345678901234.6) differs in its 16th digit, where PostgreSQL is the less exact side. Like Round's tie, that is representation, documented rather than refused (#1087).

For a scaled numeric, round to the scale first: Cast(Round(x, 2), "numeric(10,2)") builds on every engine, because Round(x, d) has at most d places, so a cast to a scale of at least d has nothing left to round. Only a decimal tie can differ, in its last digit, as Rounding to decimal places above describes (#1061). Otherwise cast to an unscaled "numeric", which keeps the value.

A DecimalField object declares its precision and scale with its type, as its column does (#1221): Cast(x, DecimalField(max_digits = 10, decimal_places = 1)) is Cast(x, "numeric(10,1)"), the same SQL on each engine and the same refusal, and DecimalField() is numeric(10,2). The same holds for an output_field. So the unscaled cast is the string "numeric", not DecimalField().

A CharField object given a max_length declares that length too (#1252), as Django's does: Cast(x, CharField(max_length = 3)) is Cast(x, "varchar(3)"), an explicit max_length = 250 included. CharField() declares no length, so it stays the unbounded text it casts to on every engine, although its column is varchar(250). A SlugField and a URLField declare the length of their column, as Django's do (#1438): SlugField() is varchar(50), URLField() is varchar(200). An EmailField has no length in PormG, so it casts to unbounded text.

An ArrayField casts to its element's type with [] (#1438): ArrayField(CharField(max_length = 3)) is varchar(3)[], which cuts each element. Arrays are PostgreSQL's, so the cast raises BackendCapabilityError on SQLite and MySQL, as the column does.

A cast to any other type ("numeric", "double precision", "float8", "date") is not checked: it is not a text conversion, and Concat refuses the result if you then use it as text.

using PormG.Functions: Cast, Ceil, Floor, Round
using PormG.Models: IntegerField

# Cast("points", IntegerField()) builds on PostgreSQL and MySQL (1.5 → 2) and is refused on SQLite,
# which would truncate it to 1. Say how to round, and it builds on every engine.
# Race 2, the 2009 Malaysian GP, was stopped early and scored half points.
M.Result.objects.filter("raceid" => 2).values(
    "resultid", "points",
    "nearest" => Cast(Round("points"), IntegerField()),   # 1.5 → 2 on every engine
    "down"    => Cast(Floor("points"), IntegerField()),   # 1.5 → 1
    "up"      => Cast(Ceil("points"), IntegerField()))    # 1.5 → 2

A cast to a decimal type ("numeric", "numeric(p, s)", "decimal", a DecimalField) reads back as a Decimals.Decimal on every engine when its operand is a number (#1212). PostgreSQL's driver delivers one, already rounded to the declared scale. MySQL computes a numeric(p, s) cast at DECIMAL(65, s), rounded to the scale, and PormG applies p as it reads the value (#1110); a bare "numeric" is DECIMAL(65, 30). SQLite computes a REAL, or an INTEGER when the operand is whole, and ignores the declaration, so PormG applies it as it reads the value (#1110): rounded to s places, half away from zero as PostgreSQL rounds, and refused with StatementError (SQLSTATE 22003) when it then needs more than p - s digits before the point. A cast with no declared width ("numeric", "decimal"), or one wider than 15 digits, is read to 15 significant digits instead, as a computed decimal is (see The type of a computed number). A cast whose operand has more places than s is still refused on SQLite when the query is built, as above.

using PormG.Functions: Cast, Round
using PormG: F
using PormG.Models: DecimalField

# Result 1 is Lewis Hamilton's win at the 2008 Australian GP: grid 1, 10 points.
M.Result.objects.filter("resultid" => 1).values(
    "grid"   => Cast("grid", DecimalField()),                         # numeric(10,2): Decimal 1 on every engine
    "scaled" => Cast(Round(F("points") * 1.5, 2), "numeric(10,2)"))   # Decimal 15 on every engine

output_field

Case, Coalesce, Greatest and Least cast their result to the output_field they are given, on every engine, as Cast does. The SQLite date rule above applies to them too: a date renders date(…), and a timestamp, time, interval or array type raises BackendCapabilityError on SQLite. The value, a filter on it, and a CTE column typed by it therefore all agree. The cast applies to the result only: a Case's literal default beside a column branch binds as that branch's type on PostgreSQL, so Case(When("result" => 1, then = F("boolean_field")), default = false; output_field = "text") renders THEN "boolean_field" ELSE $2::boolean END)::text and runs (#1222). Before the #852 fix, Coalesce, Greatest and Least rendered no cast on SQLite, and Greatest and Least rendered none on PostgreSQL either:

# The best of a result's grid slot and one, as a bigint on every engine:
# (GREATEST(…))::bigint on PostgreSQL, CAST(MAX(…) AS BIGINT) on SQLite,
# CAST(GREATEST(…) AS SIGNED) on MySQL
M.Result.objects.values("resultid", "slot" => Greatest("grid", 1; output_field = "bigint"))

On MySQL the cast is CAST(… AS T), with T the MySQL type the name maps to (see Backends), with two exceptions MySQL decides itself. MySQL has no boolean type, so a "boolean" declaration over booleans or integers reads ((x) <> 0), as PostgreSQL's integer::boolean does, and over any other value it is refused with QueryBuildError. And MySQL stores a duration as its microseconds, so over durations a text declaration reads the HH:MM:SS text, a DurationField() declaration is the value itself, and a date, time or timestamp declaration is refused.

The rule above applies to these casts too: Coalesce, Greatest, Least and Case with an output_field of text, an integer or a scaled numeric(p, s) refuse the same operands Cast does, on the engine that departs. Greatest("points", 0; output_field = "integer") is refused on SQLite, and Greatest(Floor("points"), 0; output_field = "integer") passes. A Case is held to it branch by branch, each then and its default (#1217): Case(When("grid" => 1, then = F("points") * 1.5), default = 0; output_field = "numeric(10,1)") is refused on SQLite, which would keep every digit where PostgreSQL and MySQL round. Round the branches to the scale (then = Round(F("points") * 1.5, 1)), or leave the scale off.

An output_field naming a decimal type reads back as a Decimals.Decimal on every engine, as a Cast to one does (#1212): Coalesce(F("points") * 1.5, 0; output_field = "numeric") is a Decimal on every engine. With output_field = DecimalField(), which is numeric(10,2) (#1221), the float arithmetic is held to two places and refused on SQLite; round it first, Coalesce(Round(F("points") * 1.5, 2), 0; output_field = DecimalField()). The type is the same, and so is the value wherever PormG can type the operand: a scale an engine would not round to is refused there, as described just above. A Coalesce, Greatest or Least over DecimalField columns of one width, declared at that same width (Coalesce("points", "points"; output_field = DecimalField()) over DecimalField() columns), holds a value SQLite stored, so on SQLite it reads as the column itself does. Declared at another width, or as a bare "numeric", it reads as the computed 15-digit Decimal (#1276).

Concat renders no cast on any engine, because its result is always text. Its output_field must therefore be a text type (CharField(), TextField(), "text", "varchar(20)"). Any other type raises InvalidValueError when the expression is built, since the SQL would never apply it. To get a number out of a concatenation, cast the result:

using PormG.Functions: Cast, Concat, Value

# Season 2020, round 7 → the text '202007' → the integer 202007 (rounds 1–9 only, for the padding)
M.Race.objects.filter("year" => 2020, "round__@lt" => 10).
    values("raceid", "season_round" => Cast(Concat("year", Value("0"), "round"), "integer"))

Extract — Extract Date/Time Part

using PormG.Functions: Extract

query = M.Race.objects
query.values(
    "race_year" => Extract("date", "year"),
    "race_dow"  => Extract("date", "dow")
)

The part is case-insensitive ("year" and "YEAR" are the same). YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW, DOY, WEEK, ISOYEAR and ISODOW run on every engine, numbered as PostgreSQL numbers them. Any other PostgreSQL EXTRACT field raises BackendCapabilityError on SQLite — see PostgreSQL.

MySQL also renders QUARTER, EPOCH, MILLISECONDS and MICROSECONDS, each with PostgreSQL's meaning: the two fractions count the seconds too (04.125 is 4125 milliseconds), and EPOCH is the seconds since 1970 as a Decimal, exact before 1970 as well. EPOCH of a time, or of an expression PormG cannot type, raises BackendCapabilityError there (cast an untyped value to a timestamp first). A date part of a time expression raises QueryBuildError on MySQL, which reads a time's date as today's. CENTURY, DECADE, MILLENNIUM, JULIAN and the TIMEZONE fields raise BackendCapabilityError on MySQL, whose timestamps store no zone (#1130).

A part that is not an EXTRACT field at all raises InvalidValueError when the expression is built, on every engine. The field is a keyword in the SQL and cannot be a bind parameter, so PormG only ever writes a spelling from its own list, never the text it was given. The accepted fields are CENTURY, DAY, DECADE, DOW, DOY, EPOCH, HOUR, ISODOW, ISOYEAR, JULIAN, MICROSECONDS, MILLENNIUM, MILLISECONDS, MINUTE, MONTH, QUARTER, SECOND, TIMEZONE, TIMEZONE_HOUR, TIMEZONE_MINUTE, WEEK and YEAR. PostgreSQL's plural and abbreviated synonyms (years, mon, hr, …) are not accepted — spell the field.

The part must be one its column holds — Extract("date", "hour") on a DateField raises QueryBuildError — and a filter on the result is held to the part's range. Both rules are the transforms' rules: see Which columns a transform reads.

To change the result type, wrap the extract in Cast. Four parts are decimals on every engine that has them, and read back as a Decimal. PostgreSQL renders each EXTRACT(… FROM x)::numeric, so the type is the same on every server version (extract returns a double precision before PostgreSQL 14):

A fractional part is a decimal's text, so Concat refuses it (#1027, #1552) and asks for a cast to text. A whole one, like the integer parts, builds. For whole seconds, cast EPOCH:

using PormG.Functions: Cast, Extract

# PostgreSQL and MySQL: seconds since 1970 for each race date, as a bigint
M.Race.objects.values("raceid", "start_epoch" => Cast(Extract("date", "epoch"), "bigint"))

ToChar — Format as String

using PormG.Functions: ToChar

query = M.Race.objects
query.values(
    "formatted_date" => ToChar("date", "YYYY-MM")
)

ToChar renders to_char(x, …) on PostgreSQL, strftime(…) on SQLite and DATE_FORMAT(x, …) on MySQL, and every format in the table below produces the same text on every engine for a given instant. PormG spells the format for each engine itself: HH is the 24-hour clock on each, and the T separator and the .SSS milliseconds render as written.

A duration has no calendar date and no single clock time, so ToChar over one raises QueryBuildError on every engine, whatever the format (#1416). That covers a DurationField column, interval arithmetic (F("fastestlaptime") * 2), a timestamp difference and Sum of a duration. SQLite would read the duration's text as a time of day (2000 for "YYYY", NULL past 24 hours), PostgreSQL would format the interval's own fields, which depend on how it was formed, and MySQL holds a duration as its microseconds. Cast(x, "text") gives a duration's text on every engine, and Extract(x, "epoch") gives its seconds on PostgreSQL and MySQL.

A time of day has no date, so over a TimeField (or Max of one) a format that writes a year, a month or a day raises QueryBuildError on every engine (#1336). SQLite would write 2000-01-01, PostgreSQL would read the time as an interval, and MySQL would give it today's date. The clock formats ("HH", "MI", "SS", "HH:MI", "HH:MI:SS", "HH:MI:SS.SSS") format a time on every engine: ToChar("time", "HH:MI") on M.Race.

# The 2009 Australian Grand Prix started at 06:00 UTC — the same string on every engine
query = M.Race.objects
query.filter("raceid" => 1)
query.values("start" => ToChar("start_at", "YYYY-MM-DDTHH:MI:SS.SSS"))
query.list(:dict)   # [Dict(:start => "2009-03-29T06:00:00.000")]

ToChar(x, "YYYY-MM") is the expression "x__@yyyy_mm" builds, and it follows the same rules. Its column must be a date or a timestamp, and a filter on it takes a whole "YYYY-MM" value: "2009-03", not "2009" or "March 2009". A pattern lookup takes a fragment of that text instead, so "ym__@startswith" => "2009" selects the 2009 months.

Case / When Expressions

Case and When enable SQL CASE WHEN ... THEN ... ELSE ... END expressions.

Plain Julia values — including strings — can be passed to then, otherwise, and default directly. No Value() wrapper is required.

A When with no otherwise is a Case branch, not a value. Used on its own, for example Count(When("positionorder" => 1, then = 1)) or "c" => When(…) in values, it raises QueryBuildError when the query is built: alone it would render WHEN … THEN … with no ELSE and no END, which no engine parses. Give it its own otherwise, or put it in a Case with a default.

Binary When (single condition, two outcomes)

For a simple yes/no expression, pass otherwise directly to When. PormG wraps it in a full CASE … END automatically — no Case wrapper needed:

using PormG.Functions: When

# Did the driver win at least one race in their standing?
query = M.Driver_standings.objects
query.values(
    "driverid__surname",
    "points",
    "wins",
    "race_winner" => When("wins__@gt" => 16, then = "Yes", otherwise = "No")
)
query.filter("raceid__year" => 2023)
query.order_by("-points").limit(5)
df = query |> DataFrame

Generated SQL (PostgreSQL):

SELECT "driver"."surname"           AS driverid__surname,
       "driver_standings"."points"  AS points,
       "driver_standings"."wins"    AS wins,
       CASE WHEN "driver_standings"."wins" > $1
            THEN $2::text
            ELSE $3::text
       END                          AS race_winner
FROM "driver_standings"
INNER JOIN "driver" ON "driver_standings"."driverid" = "driver"."driverid"
INNER JOIN "race"   ON "driver_standings"."raceid"   = "race"."raceid"
WHERE "race"."year" = $4
ORDER BY "points" DESC
LIMIT $5
-- parameters: [16, "Yes", "No", 2023, 5]

Output:

5×4 DataFrame
 Row │ driverid__surname  points    wins    race_winner
     │ String?            Float64?  Int32?  String?
─────┼──────────────────────────────────────────────────
   1 │ Verstappen            575.0      19  Yes
   2 │ Verstappen            549.0      18  Yes
   3 │ Verstappen            524.0      17  Yes
   4 │ Verstappen            491.0      16  No
   5 │ Verstappen            466.0      15  No

Multi-branch Case Expression

For multiple conditions, wrap a vector of When fragments in Case. The default on Case provides the ELSE branch:

using PormG.Functions: Case, When

query = M.Driver.objects
query.values(
    "surname",
    "region" => Case([
        When("nationality" => "British",     then = "UK"),
        When("nationality__@in" => ["French", "Italian", "Spanish"], then = "Europe"),
        When("nationality" => "Brazilian",   then = "South America")
    ], default = "Other")
)
query.limit(10)
df = query |> DataFrame

Output:

10×2 DataFrame
 Row │ surname    region
     │ String?    String?
─────┼────────────────────
   1 │ Hamilton   UK
   2 │ Heidfeld   Other
   3 │ Rosberg    Other
  ⋮  │     ⋮         ⋮
   8 │ Räikkönen  Other
   9 │ Kubica     Other
  10 │ Glock      Other
              4 rows omitted

Case with Q() and F() Logic

For more complex conditions, combine Case/When with Q() for boolean logic and F() for field references. An arithmetic F expression is not a When condition: When(F("laps") - 50, …) is refused, and When((F("laps") - 50) > 0, …) is the spelling (see Arithmetic is not a condition). The same goes for a function whose result is not boolean: When(Lower("surname"), …) is refused, and When(Lower("surname") == "senna", …) is the spelling.

using PormG: Q, F
using PormG.Functions: Case, When, Sum, Value

query = M.Result.objects
query.filter("driverid__forename" => "Mika")
query.values(
    "raceid__circuitid__name",
    "under_30_victories" => Sum(
        Case(
            When(
                Q(
                    F("raceid__date") <= F("driverid__dob") + 10957,  # ~30 years in days
                    "positionorder" => 1
                ),
                then = 1
            ),
            default = 0
        )
    )
).filter("under_30_victories__@gt" => 0)
df = query |> DataFrame

Generated SQL (PostgreSQL):

SELECT "circuit"."name"  AS raceid__circuitid__name,
       SUM(CASE WHEN ("race"."date" <= (("driver"."dob" + make_interval(days => $1::integer)))::date
                 AND  "result"."positionorder" = $2)
                THEN $3::bigint
                ELSE $4::bigint
       END)              AS under_30_victories
FROM "result"
INNER JOIN "race"    ON "result"."raceid"   = "race"."raceid"
INNER JOIN "circuit" ON "race"."circuitid"  = "circuit"."circuitid"
INNER JOIN "driver"  ON "result"."driverid" = "driver"."driverid"
WHERE "driver"."forename" = $5
GROUP BY 1
HAVING SUM(CASE WHEN ... END) > $6
-- parameters: [10957, 1, 1, 0, "Mika", 0]

Output:

8×2 DataFrame
 Row │ raceid__circuitid__name          under_30_victories
     │ Union{Missing, String}           Decimals.Decimal?
─────┼─────────────────────────────────────────────────────
   1 │ Albert Park Grand Prix Circuit                    1
   2 │ Autódromo José Carlos Pace                        1
   3 │ Circuit de Barcelona-Catalunya                    1
   4 │ Circuit de Monaco                                 1
   5 │ Circuito de Jerez                                 1
   6 │ Hockenheimring                                    1
   7 │ Nürburgring                                       1
   8 │ Red Bull Ring                                     1

This generates a SUM(CASE WHEN ... THEN 1 ELSE 0 END) pattern — very useful for computing conditional counts within grouped queries.

A Column as the Branch Value

then, default and otherwise take a column expression as well as a value. A plain value is bound as a parameter; F("points") renders as the column itself, so the branch returns each row's own value. That turns the conditional count above into a conditional sum:

using PormG: F
using PormG.Functions: Case, When, Sum

# Points each driver scored in the races they won, 2009 season
query = M.Result.objects
query.filter("raceid__year" => 2009)
query.values(
    "driverid__surname",
    "win_pts" => Sum(Case([When("positionorder" => 1, then = F("points"))], default = 0))
)
query.filter("win_pts__@gt" => 0)
query.order_by("-win_pts")
df = query |> DataFrame

Generated SQL (PostgreSQL):

SELECT "Tb_1"."surname" AS driverid__surname,
       SUM(CASE WHEN "Tb"."positionorder" = $1 THEN "Tb"."points" ELSE $2::bigint END) AS win_pts
FROM "result" AS "Tb"
INNER JOIN "driver" AS "Tb_1" ON "Tb"."driverid" = "Tb_1"."driverid"
INNER JOIN "race"   AS "Tb_2" ON "Tb"."raceid"   = "Tb_2"."raceid"
WHERE "Tb_2"."year" = $3
GROUP BY 1
HAVING SUM(CASE WHEN "Tb"."positionorder" = $4 THEN "Tb"."points" ELSE $5::bigint END) > $6
ORDER BY "win_pts" DESC NULLS FIRST

Output:

6×2 DataFrame
 Row │ driverid__surname  win_pts
─────┼────────────────────────────
   1 │ Button                55.0
   2 │ Vettel                40.0
   3 │ Webber                20.0
   4 │ Hamilton              20.0
   5 │ Barrichello           20.0
   6 │ Räikkönen             10.0

THEN "Tb"."points" carries no placeholder: the branch is a column, not a value. Button's 55.0 is his six 2009 wins, one of them the half-points Malaysian Grand Prix. Arithmetic works in the branch too — then = F("points") * 2 binds the 2 in its place in the SQL text, after the condition's own value.

The column in the branch is part of the projection, so it groups like one: beside an aggregate it must sit inside the aggregate (as F("points") does here, inside Sum) or be listed in values() — see A Column Beside an Aggregate in One Expression.

Case in Filters

Case expressions can be used as the right-hand side of a filter() predicate to apply dynamic thresholds. For example, the F1 points system awarded points to the top 10 finishers from 2010 onwards, but only the top 8 before that:

using PormG.Functions: Case, When

# Keep only results where the driver finished inside the points-scoring positions,
# applying the correct threshold for each era.
query = M.Result.objects
query.filter(
    "positionorder__@lte" => Case([
        When("raceid__year__@gte" => 2010, then = 10),  # modern era: top 10
    ], default = 8)                                     # classic era: top 8
)
query.values("raceid__year", "driverid__surname", "positionorder", "points")
query.order_by("raceid__year", "positionorder")
query.limit(5)
df = query |> DataFrame

Generated SQL (PostgreSQL):

SELECT "race"."year"        AS raceid__year,
       "driver"."surname"   AS driverid__surname,
       "result"."positionorder" AS positionorder,
       "result"."points"    AS points
FROM "result"
INNER JOIN "race"   ON "result"."raceid"   = "race"."raceid"
INNER JOIN "driver" ON "result"."driverid" = "driver"."driverid"
WHERE "result"."positionorder" <= CASE
    WHEN "race"."year" >= $1 THEN $2::bigint
    ELSE $3::bigint
END
ORDER BY raceid__year ASC, positionorder ASC
LIMIT $4
-- parameters: [2010, 10, 8, 5]

Output:

5×4 DataFrame
 Row │ raceid__year  driverid__surname  positionorder  points
     │ Int32?        String?            Int32?         Float64?
─────┼──────────────────────────────────────────────────────────
   1 │         1950  Farina                         1       9.0
   2 │         1950  Fangio                         1       9.0
   3 │         1950  Farina                         1       9.0
   4 │         1950  Parsons                        1       9.0
   5 │         1950  Fangio                         1       8.0

All rows are from 1950 (classic era), so the CASE evaluates to ELSE 8 — only finishers in positions 1–8 are returned. The CASE expression is evaluated per row against each race's own year, so a modern race would use threshold 10 and a classic race would use threshold 8.

Combining Functions

Functions can be nested and combined with aggregates:

using PormG.Functions: Count, Concat, Value, Upper

# Count races per nationality, with formatted output
query = M.Driver.objects
query.values(
    "region" => Upper("nationality"),
    "driver_count" => Count("driverid")
)
query.order_by("-driver_count")
query.limit(10)
df = query |> DataFrame

Generated SQL:

SELECT UPPER("driver"."nationality") AS region,
       COUNT("driver"."driverid")    AS driver_count
FROM "driver"
GROUP BY 1
ORDER BY "driver_count" DESC
LIMIT $1
-- parameters: [10]

Output:

10×2 DataFrame
 Row │ region         driver_count
     │ String?        Int64?
─────┼─────────────────────────────
   1 │ BRITISH                 166
   2 │ AMERICAN                158
   3 │ ITALIAN                  99
  ⋮  │       ⋮             ⋮
   8 │ BELGIAN                  23
   9 │ SWISS                    23
  10 │ SOUTH AFRICAN            23
               4 rows omitted

Next Steps

• Subqueries and CTEs — Decompose complex queries with WITH clauses.
• Field Expressions — Database-side arithmetic and field-to-field comparisons.
• Window Functions — Rank, Lag, Lead, and friends for per-row analytics.
• Filters and Aggregates — Lookup operators and HAVING clause details.