SQL forms

A SQL form is a PostgreSQL 16 expression that gives exactly the result the runtime gives, for every argument. It lets the database do work that would otherwise take a round trip through your app. It matters in three places:

• Computed backfills. A backfill whose every operation has a SQL form runs as one UPDATE per batch. Any other runs through jadpo migrate apply, reading and writing each batch (Computed backfills). Both are correct; the SQL form is only faster.
• Aggregate queries. An aggregate query (Counts, sums and groups) is always one statement. It writes its time buckets (temporal.year, temporal.month and temporal.day of a date) and its averages (decimal.divide) with their SQL forms.
• Conditions. A query's condition may compare an operation of a field that has a SQL form, with its other arguments written out, through an index on the form (Operations in conditions). Otherwise a query's conditions compute nothing, and a one-step update's set: still works a stored field out only with + and - (Counters and other one-step updates).

The rules every SQL form follows:

• An operation that can fail has no SQL form, since a statement can't map a failure to a row.
• Some arguments have no form. An operation's form is written from its arguments: each a SQL expression and how it is held (a built-in type's column, JSON, a range of a built-in type), money's two columns, or what a call writes out, such as a rounding mode, decimal.round's scale: or text.pad_start's fill:. An operation has no form for arguments the tables don't cover: a rounding mode that isn't written out, text.from with form: or of a time, or hash.sha256 of a secret value.
• Given values, never NULL. Code tests a value that may be none before calling an operation on it, so a form never decides what none means. range.intersection, whose result may be none, gives NULL for it.
• A whole number is always bigint. A field with a narrow range is stored in a smallint or an integer (Storage types), whose abs and / overflow at -32768 where an Int's don't. So integer's operations widen their arguments first, and char_length and jsonb_array_length are cast, so arithmetic on what they give overflows exactly where the runtime's does. A value tested against a range is cast to the range's end type for the same reason.
• A decimal keeps the places the runtime keeps. A numeric carries its places as a runtime Decimal does, so the forms read them with scale(). decimal.round keeps a value with at most scale: places as it is, where round(x, s) adds zeros (1.5 to two places stays 1.5). decimal.divide works the quotient out from the two mantissas with div and mod, exactly, as the runtime does, never with PostgreSQL's /, which rounds to the places it chooses.
• A defect stops the statement. Where the runtime stops with a defect, a whole number past 64 bits or a decimal past 38 digits, the form fails too: bigint overflows where an Int does, and decimal.divide's mantissa passes through numeric(38, 0), so a backfill stops at the row the runtime would.
• temporal.period_between without age. The form counts the months between the two months, one fewer when the end's day of the month hasn't reached the start's (one more going back), then steps once toward zero where adding them to the start, years first and then months, each clamped to a month's last day, would pass the end; the days are what is left. The runtime steps until it stops passing; once is always enough, since a clamped day is never past the start's. Year 0 is PostgreSQL's 1 BC, whose extract gives -1, so the form counts from year 0 as the runtime does. It names its two dates once, in a subquery, and gives the three columns a CalendarPeriod is stored in.
• Counts and positions clamped to integer. left, right, substr and lpad take PostgreSQL's 32-bit integer; text is never longer than a gigabyte, so a larger count means what the largest does and is clamped rather than cast, which would fail.
• Text in code points, whatever the collation. Text is compared and ordered in code points, as queries sort it (What can be sorted), with COLLATE "C" indexes (Indexes). So a SQL form never depends on the database's collation. That rules out lower and upper: PostgreSQL 16's follow the collation, and under "C" they change only ASCII letters. ends_with compares with COLLATE "C"; strpos, starts_with, replace and btrim match code points already. trim's characters are Unicode's 25 White_Space characters, which Rust's str::trim removes.
• No session setting changes a form. Dates are added without a zone, and escapes are written E'...', so a session's time zone, date style, interval style and standard_conforming_strings make no difference.
• Lists, sets and maps. A list or set is stored as a jsonb array and a map as a jsonb object (Data); only count has a SQL form.
• Each form is tested against the runtime before anything uses it. Each operation with a form is run in the runtime and as its form, on the same columns a backfill reads, and the two results must match, places and widths included. An input the runtime stops on must fail the statement. Every form in the tables below is tested over a hundred thousand inputs on live PostgreSQL.

A backfill's operators and conditionals have SQL forms too, tested against the runtime the same way. In this table a and b are their operands:

A decimal's * and /, in, matches, and the operators on money, quantities, percentages, periods and times moved by a duration have no form yet.

In the tables below, x is the first argument and a, b the others.