snippets

package
v0.1.1 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Oct 11, 2026 License: Apache-2.0 Imports: 0 Imported by: 0

README

snippets

A catalogue of advanced PostgreSQL snippets, translated into sqlb section by section, in the catalogue's own order and numbering: 21 sections and 193 SQL blocks. Read the test files top to bottom and they are one document. Every ### x.y heading appears in order, and each is either an Example whose SQL go test checks, or a comment that says why there is nothing to render.

Layout

helpers_test.go        tbl, ref, lit, join and order shorthands, the show printer
s01_recursive_test.go  1. Recursive CTEs and graphs
s02_window_test.go     2. Window functions
s03_lateral_test.go    3. LATERAL and set-returning functions
s04_aggregation_test.go 4. Aggregation
s05_writes_test.go     5. Writes: upsert, MERGE, data-modifying CTEs
s06_json_test.go       6. JSON and JSONB
s07_arrays_test.go     7. Arrays
s08_ranges_test.go     8. Range types, exclusion constraints, temporal modelling
s09_search_test.go     9. Full-text and fuzzy search
s10_time_test.go       10. Date and time edge cases
s11_edge_test.go       11. NULL, numeric, and string edge cases
s12_locking_test.go    12. Locking, concurrency, queues
s13_s17_ddl_ops_test.go 13. Partitioning and DDL, 14. Triggers and PL/pgSQL, 15. Security, 16. Performance, 17. Operations
s18_s19_pivot_analytics_test.go 18. Pivot, unpivot, dedup, test data; 19. Analytics patterns
s20_s21_grabbag_test.go 20. Grab bag; 21. Anti-pattern cheat sheet
Count
Headings in the catalogue, all present in order 201
Examples with checked SQL output 159
Headings kept as comments because the SQL is DDL, PL/pgSQL, psql, session control or administration 42

Run go test ./pgsurface/snippets -run Example -v from design/ to print every rendered statement.

How to read a comparison

Take a heading in the markdown, find the same heading in the test file, and compare three things: the Go that builds the statement, the SQL in the // Output: block, and the SQL in the markdown. The Go should read like the SQL. Where the text differs, it differs on purpose: sqlb quotes every identifier, binds every value, parenthesises every binary expression and every AND group, and writes CAST(x AS t) rather than x::t. The statement is the same; the bytes are not.

Every construct in the catalogue has a named helper or a core constructor. The first pass left seven kinds of construct on the typed door, sqlb.New, sqlb.Raw, sqlb.SourceOf, sqlb.Compare; the second pass added a helper for each and rewrote the examples, so no example builds SQL from a string any more. What still calls sqlb.Fn by name is a user-defined function from the catalogue (uuid_generate_v7, count_where, top_customers, dblink_exec) or a set-returning function used as a source with the caller's own column names, which is what pg.FuncSource plus sqlb.Fn is for.

What the catalogue added to the builder

Six gaps were universal SQL and went into the core: a SELECT with no FROM, a suffix on a recursive CTE for SEARCH and CYCLE, a frame bound with an expression offset for RANGE ... interval PRECEDING, INSERT ... SELECT without a column list, NotEqExpr with the <= and >= expression forms, InExprs, AssertNotNull, and an assignment target that is an expression, for JSONB subscript assignment.

The Postgres-only pieces went into pg, by area:

  • SQL/JSON and JSONB. JSONTable with typed, nested and ordinality columns; JSONObject, JSONArrayAgg, JSONObjectAgg with ABSENT ON NULL, WITH UNIQUE KEYS and RETURNING; IsJSON; JSONValue, JSONQuery, JSONExists; JSONSubscript and SetSubscript; PathQueryVars, PathQueryArray, PathQueryScalar, JSONBSetExpr, JSONBSetLax; JSONNull, EmptyJSONArray, RowRef.
  • Sources. RowsFrom, UnnestMulti, RecordSet with a typed column list, GenerateSeriesTime, GenerateSeriesExpr, GenerateSeriesScalar, StringToTable, Variadic and Named arguments.
  • Recursion. SearchDepthFirst, SearchBreadthFirst, Cycle.
  • Text. SubstringRegex, TrimChars, Normalize, Encode, RegexpInstr, RegexpSubstr, OctetLength, QuoteIdent, QuoteLiteral, RepeatExpr, SubstrExpr, StringToArrayNull.
  • Search. TSRankCDNormalized in pg; the trigram, phonetic and ltree helpers are in the extension packages below.
  • Arrays. ArrayOf, AnyExpr, AllExpr, OverlapsExpr, ArrayContainsExpr, ArrayContainedByExpr, SliceFrom, SliceTo, SliceExprs, SubscriptExpr, ArrayPositions, ArrayRemoveNull, EmptyArray, ArrayLit, UnnestScalar, WidthBucketThresholds.
  • Time. IntervalLit, IntervalMul, AddIntervalExpr, SubIntervalExpr, JustifyInterval, ExtractFrom, DateTruncZone, DateLit, TimestampLit, TimestamptzLit, Infinity, OverlapsInterval, DaysBetween, DatePlusDays, DateMinusDays, FieldIsoDow and friends.
  • Ranges. DateRange, NumRange, Int4Range, TstzRangeExpr, NullBound, UpperInf, LowerInf, Multirange, TstzMultirange, MultirangeDiff, RangeAggTyped, RangeIntersectAgg, UnnestMultirange.
  • Numbers and bits. Div, ModFn, RoundHalf, TruncZero, PowerOp, PowerExpr, Radians, Sin, Cos, Asin, RandomNormal, Setseed, BitAnd, BitOr, BitXor, BitNot, ShiftLeft, ShiftRight, BitLit, BitStringOr, GetBit, BitCount.
  • Locks, session, catalog. AdvisoryXactLock, AdvisoryXactLock2, TryAdvisoryXactLock, Hashtext, Regclass, SizePretty, TotalRelationSize, RelationSize, IndexesSize, ColumnSize, XidAge, FormatType, CancelBackend, TerminateBackend, BlockingPids, InputIsValid, CurrentDatabase, HasRole, CurrentXactID, TxidCurrent, ExportSnapshot, CurrentWalLsn, WalLsnDiff, LastXactReplayTimestamp, GetSerialSequence, Setval.
  • Extensions. Each in its own sub-package with a README: pg/trgm (Similar, Similarity, Distance, WordSimilarity), pg/fuzzy (Dmetaphone, LevenshteinLessEqual), pg/ltree (Path, Lit, AncestorOf, DescendantOf, MatchesLQuery, NLevel, Subpath, Index), pg/crypto (Crypt, GenSalt, Digest, HMAC, PgpSymEncrypt, PgpSymDecrypt), pg/earth (LLToEarth, Box, BoxContains), pg/unaccent (Unaccent). UUIDv7Timestamp needs no extension and stays in pg.

What is not proven

Rendering and compile-time checks only. Nothing here has run against a Postgres server. The catalogue marks which of its snippets were executed against PostgreSQL 17; the translations inherit no such mark until the integration module runs them.

Documentation

Overview

Package snippets translates a catalogue of advanced PostgreSQL snippets into sqlb, section by section and in the catalogue's own order and numbering.

The test files are its sections: s01 to s21, one file per section or group of sections, 21 sections and 193 SQL blocks in all. Every "### x.y" heading appears in the same order as a comment, so the files can be read straight through as one document. A heading whose SQL is a query is an Example whose Output block go test checks. A heading whose SQL is DDL, a procedure, a psql command or an administration statement keeps its place as a comment that says which kind it is, because those are not the query layer and sqlb does not render them.

Conventions, stated once:

  • Tables are declared inline in each example, as the snippets do.
  • Most examples build the select list with Exprs, which carries no scan targets; a few use Bind to show the typed scan. Rendering is the same.
  • A construct with no pg helper goes through a typed door, sqlb.Fn, sqlb.New, sqlb.Raw or sqlb.SourceOf, and the comment says so.
  • Literal constants that the snippet writes inline, such as an interval or a jsonpath, are author constants here too; request values are bound.

None of this SQL has run against a Postgres server from here.

Example (S10_01_atTimeZoneFlip)

### 10.1 timestamp versus timestamptz and the AT TIME ZONE flip

utc := pg.AtTimeZone(pg.Now(), "UTC")
show(sel().Exprs(utc, pg.AtTimeZone(utc, "Asia/Kolkata"), sqlb.BoolExpr(sqlb.EqExpr(pg.AtTimeZone(utc, "UTC"), pg.Now()))).Build())
Output:
SELECT now() AT TIME ZONE $1, now() AT TIME ZONE $2 AT TIME ZONE $3, now() AT TIME ZONE $4 AT TIME ZONE $5 = now()
args: 5
Example (S10_02_dstDayVersusHours)

### 10.2 DST: '1 day' is not '24 hours' SET timezone is session state, not a query. The comparison renders.

v := pg.Values("v", []string{"ts"}, []sqlb.SQLer{pg.TimestamptzLit("2024-03-09 12:00")})
ts := ref[time.Time](v, "ts")
show(sel().Exprs(ts, sqlb.As(pg.AddIntervalExpr(ts, pg.IntervalLit("1 day")), "next_day_same_wall_clock"),
	sqlb.As(pg.AddIntervalExpr(ts, pg.IntervalLit("24 hours")), "plus_24h")).From(v).Build())
Output:
SELECT "v"."ts", ("v"."ts" + interval '1 day') AS "next_day_same_wall_clock", ("v"."ts" + interval '24 hours') AS "plus_24h" FROM (VALUES ('2024-03-09 12:00'::timestamptz)) AS "v" ("ts")
Example (S10_03_bucketing)

### 10.3 Bucketing: date_trunc with a zone, date_bin with an origin

ev := tbl("events", "events")
created := sqlb.Col[time.Time](ev, "created_at")
show(sel().Exprs(sqlb.As(pg.DateTruncZone(pg.FieldDay, created, "America/New_York"), "local_day")).From(ev).Build())
show(sel().Exprs(sqlb.As(pg.DateBin("15 minutes", created, t0), "bucket")).From(ev).Build())
t := tbl("t", "t")
dd := sqlb.Col[time.Time](t, "d")
show(sel().Exprs(sqlb.As(pg.DateTrunc(pg.FieldWeek, dd), "iso_monday"), sqlb.As(pg.ToChar(dd, "IYYY-IW"), "iso_week")).From(t).Build())
Output:
SELECT date_trunc('day', "events"."created_at", $1) AS "local_day" FROM "events" AS "events"
args: 1
SELECT date_bin($1::interval, "events"."created_at", $2) AS "bucket" FROM "events" AS "events"
args: 2
SELECT date_trunc('week', "t"."d") AS "iso_monday", to_char("t"."d", $1) AS "iso_week" FROM "t" AS "t"
args: 1
Example (S10_04_monthArithmetic)

### 10.4 Month arithmetic is not reversible

jan31 := pg.DateLit("2024-01-31")
month := pg.IntervalLit("1 month")
show(sel().Exprs(pg.AddIntervalExpr(jan31, month), pg.SubIntervalExpr(pg.AddIntervalExpr(jan31, month), month),
	sqlb.Cast[time.Time](pg.AddIntervalExpr(pg.DateTrunc(pg.FieldMonth, pg.DateLit("2024-02-10")), pg.IntervalLit("1 month - 1 day")), "date")).Build())
Output:
SELECT (date '2024-01-31' + interval '1 month'), ((date '2024-01-31' + interval '1 month') - interval '1 month'), CAST((date_trunc('month', date '2024-02-10') + interval '1 month - 1 day') AS date)
Example (S10_05_intervalComparison)

### 10.5 Interval comparison is fuzzy

show(sel().Exprs(
	sqlb.BoolExpr(sqlb.EqExpr(pg.IntervalLit("1 month"), pg.IntervalLit("30 days"))),
	sqlb.BoolExpr(sqlb.EqExpr(pg.IntervalLit("1 day"), pg.IntervalLit("24 hours"))),
	pg.JustifyInterval(pg.IntervalLit("35 days 26 hours")),
	pg.ExtractFrom(pg.FieldEpoch, pg.IntervalLit("1 month")),
).Build())
Output:
SELECT interval '1 month' = interval '30 days', interval '1 day' = interval '24 hours', justify_interval(interval '35 days 26 hours'), extract(epoch FROM interval '1 month')
Example (S10_06_frozenNow)

### 10.6 now() is frozen for the whole transaction BEGIN, pg_sleep and COMMIT are session control. The three clocks render.

show(sel().Exprs(pg.Now(), pg.StatementTimestamp(), pg.ClockTimestamp()).Build())
Output:
SELECT now(), statement_timestamp(), clock_timestamp()
Example (S10_07_businessDays)

### 10.7 Business days between two dates

days, day := pg.GenerateSeriesExpr(sqlb.Cast[time.Time](sqlb.Param(t0), "date"), pg.DateMinusDays(sqlb.Cast[time.Time](sqlb.Param(t1), "date"), sqlb.Param(1)), pg.IntervalLit("1 day"), "d")
h := tbl("holidays", "h")
show(sel().Exprs(sqlb.As(sqlb.Count(), "business_days")).From(days).
	Where(sqlb.Less(pg.Extract(pg.FieldIsoDow, day), 6),
		sqlb.NotExists(sel().Exprs(sqlb.Param(1)).From(h).Where(sqlb.EqExpr(sqlb.Col[time.Time](h, "holiday"), sqlb.Cast[time.Time](day, "date"))))).Build())
Output:
SELECT count(*) AS "business_days" FROM generate_series(CAST($1 AS date), (CAST($2 AS date) - $3), interval '1 day') AS "d" ("ts") WHERE (extract(isodow FROM "d"."ts") < $4 AND NOT EXISTS (SELECT $5 FROM "holidays" AS "h" WHERE ("h"."holiday" = CAST("d"."ts" AS date))))
args: 5
Example (S10_08_thirdFriday)

### 10.8 Nth weekday of a month (third Friday)

v := pg.Values("v", []string{"d"}, []sqlb.SQLer{pg.DateLit("2024-09-10")})
dd := ref[time.Time](v, "d")
first := pg.DateTrunc(pg.FieldMonth, dd)
// ((5 - isodow(first) + 7) % 7) * interval '1 day' + interval '2 weeks'
offset := sqlb.Mod(sqlb.Add(sqlb.Sub(sqlb.Param[int32](5), sqlb.Cast[int32](pg.Extract(pg.FieldIsoDow, first), "int")), sqlb.Param[int32](7)), sqlb.Param[int32](7))
shift := pg.IntervalMul(pg.IntervalLit("1 day"), offset)
show(sel().Exprs(sqlb.As(sqlb.Cast[time.Time](pg.AddIntervalExpr(pg.AddIntervalExpr(first, shift), pg.IntervalLit("2 weeks")), "date"), "third_friday")).From(v).Build())
Output:
SELECT CAST(((date_trunc('month', "v"."d") + (((($1 - CAST(extract(isodow FROM date_trunc('month', "v"."d")) AS int)) + $2) % $3) * interval '1 day')) + interval '2 weeks') AS date) AS "third_friday" FROM (VALUES (date '2024-09-10')) AS "v" ("d")
args: 3
Example (S10_09_ageDowEpoch)

### 10.9 Age, dow, epoch, and formatting traps

mar1, jan31 := pg.TimestampLit("2024-03-01"), pg.TimestampLit("2024-01-31")
show(sel().Exprs(
	pg.Age(mar1, jan31), pg.Diff(mar1, jan31),
	pg.DaysBetween(pg.DateLit("2024-03-01"), pg.DateLit("2024-01-31")),
	sqlb.As(pg.ExtractFrom(pg.FieldYear, pg.Age(pg.CurrentDate(), pg.DateLit("1990-06-15"))), "age_years"),
	pg.Extract(pg.FieldDow, pg.DateLit("2024-09-15")), pg.Extract(pg.FieldIsoDow, pg.DateLit("2024-09-15")),
	pg.EpochSeconds(sqlb.Param(1726500000.123)), pg.EpochSeconds(sqlb.Div(sqlb.Cast[float64](sqlb.Param[int64](1726500000123), "float8"), sqlb.Param(1000.0))),
	pg.ToChar(pg.Now(), `YYYY-MM-DD"T"HH24:MI:SS.MSOF`),
	sqlb.BoolExpr(sqlb.GreaterExpr(pg.Infinity(), pg.Now())),
).Build())
Output:
SELECT age(timestamp '2024-03-01', timestamp '2024-01-31'), (timestamp '2024-03-01' - timestamp '2024-01-31'), (date '2024-03-01' - date '2024-01-31'), extract(year FROM age(current_date, date '1990-06-15')) AS "age_years", extract(dow FROM date '2024-09-15'), extract(isodow FROM date '2024-09-15'), to_timestamp($1), to_timestamp((CAST($2 AS float8) / $3)), to_char(now(), $4), 'infinity'::timestamptz > now()
args: 4
Example (S10_10_overlaps)

### 10.10 OVERLAPS and half-open semantics

show(sel().Exprs(sqlb.BoolExpr(pg.OverlapsPeriod(pg.DateLit("2024-01-01"), pg.DateLit("2024-01-10"), pg.DateLit("2024-01-10"), pg.DateLit("2024-01-20")))).Build())
show(sel().Exprs(sqlb.BoolExpr(pg.OverlapsInterval(pg.TimestampLit("2024-01-01"), pg.IntervalLit("3 days"), pg.TimestampLit("2024-01-03"), pg.IntervalLit("1 day")))).Build())
Output:
SELECT (date '2024-01-01', date '2024-01-10') OVERLAPS (date '2024-01-10', date '2024-01-20')
SELECT (timestamp '2024-01-01', interval '3 days') OVERLAPS (timestamp '2024-01-03', interval '1 day')
Example (S10_11_immutableDateExpression)

### 10.11 Immutable date expression for an index on timestamptz CREATE INDEX is DDL. The query must repeat the indexed expression exactly.

ev := tbl("events", "events")
created := sqlb.Col[time.Time](ev, "created_at")
show(sel().Exprs(created).From(ev).Where(sqlb.Eq(sqlb.Cast[time.Time](pg.AtTimeZone(created, "UTC"), "date"), t1)).Build())
Output:
SELECT "events"."created_at" FROM "events" AS "events" WHERE (CAST("events"."created_at" AT TIME ZONE $1 AS date) = $2)
args: 2
Example (S11_01_notInWithNull)

### 11.1 NOT IN with a NULL returns nothing

u := tbl("users", "u")
uID := sqlb.Col[int64](u, "id")
t := tbl("teams", "t")
mgr := sqlb.Col[*int64](t, "manager_id")
show(sel().Exprs(uID).From(u).Where(sqlb.NotInSubquery(sqlb.Nullable(uID), sel().Exprs(mgr).From(t))).Build())
show(sel().Exprs(uID).From(u).Where(sqlb.NotExists(sel().Exprs(sqlb.Param(1)).From(t).Where(sqlb.EqExpr(mgr, sqlb.Nullable(uID))))).Build())
Output:
SELECT "u"."id" FROM "users" AS "u" WHERE ("u"."id" NOT IN (SELECT "t"."manager_id" FROM "teams" AS "t"))
SELECT "u"."id" FROM "users" AS "u" WHERE (NOT EXISTS (SELECT $1 FROM "teams" AS "t" WHERE ("t"."manager_id" = "u"."id")))
args: 1
Example (S11_02_nullSafe)

### 11.2 NULL-safe comparison, unique NULLs, NULL ordering The two CREATE statements are DDL. The three queries render.

a, b := tbl("a", "a"), tbl("b", "b")
aCode, bCode := sqlb.Col[*string](a, "code"), sqlb.Col[*string](b, "code")
show(sel().Exprs(aCode).From(a).Join(inner(b, pg.IsNotDistinctFrom(aCode, bCode))).Build())
t := tbl("t", "t")
score, flag := sqlb.Col[*float64](t, "score"), sqlb.Col[bool](t, "flag")
show(sel().Exprs(score).From(t).Order(pg.OrderByNulls(score, sqlb.SortDesc, false)).Build())
show(sel().Exprs(sqlb.BoolExpr(pg.IsTrue(flag)), sqlb.BoolExpr(sqlb.Eq(flag, true))).From(t).Build())
Output:
SELECT "a"."code" FROM "a" AS "a" INNER JOIN "b" AS "b" ON (("a"."code" IS NOT DISTINCT FROM "b"."code"))
SELECT "t"."score" FROM "t" AS "t" ORDER BY "t"."score" DESC NULLS LAST
SELECT "t"."flag" IS TRUE, "t"."flag" = $1 FROM "t" AS "t"
args: 1
Example (S11_03_aggregatesOverEmpty)

### 11.3 Aggregates over empty input

t := tbl("t", "t")
x := sqlb.Col[int64](t, "x")
show(sel().Exprs(sqlb.Count(), sqlb.CountOf(x), sqlb.Sum(x), sqlb.Avg(x), pg.BoolAnd(sqlb.BoolExpr(sqlb.Greater(x, 0))), pg.ArrayAgg(x),
	pg.StringAgg(sqlb.Cast[string](x, "text"), ",")).From(t).Where(sqlb.False()).Build())
Output:
SELECT count(*), count("t"."x"), sum("t"."x"), avg("t"."x"), bool_and("t"."x" > $1), array_agg("t"."x"), string_agg(CAST("t"."x" AS text), $2) FROM "t" AS "t" WHERE (FALSE)
args: 2
Example (S11_04_divisionAndRounding)

### 11.4 Integer division, modulo sign, rounding modes

ten, four := sqlb.Param[int32](10), sqlb.Param[int32](4)
show(sel().Exprs(sqlb.Div(ten, four), sqlb.Div(sqlb.Cast[float64](ten, "float8"), sqlb.Param(4.0)), sqlb.Div(sqlb.Cast[float64](ten, "numeric"), sqlb.Cast[float64](four, "numeric")),
	pg.Div(ten, four), pg.ModFn(ten, four),
	sqlb.Mod(ten, sqlb.Param[int32](-3)), sqlb.Mod(sqlb.Param[int32](-10), sqlb.Param[int32](3)),
	pg.RoundHalf(sqlb.Literal[float64]("2.5::numeric")), pg.RoundHalf(sqlb.Literal[float64]("2.5::float8")), pg.RoundHalf(sqlb.Literal[float64]("0.5::float8")),
	pg.Round(sqlb.Param(1234.5678), -2),
	pg.TruncZero(sqlb.Param(-1.7)), pg.Ceil(sqlb.Param(-1.7)), pg.Floor(sqlb.Param(-1.7))).Build())
t := tbl("t", "t")
x, divisor := sqlb.Col[float64](t, "x"), sqlb.Col[float64](t, "divisor")
show(sel().Exprs(pg.Round(x, 2)).From(t).Build())
show(sel().Exprs(sqlb.Div(sqlb.Param(1.0), sqlb.AssertNotNull(sqlb.NullIf(divisor, sqlb.Param(0.0))))).From(t).Build())
Output:
SELECT ($1 / $2), (CAST($3 AS float8) / $4), (CAST($5 AS numeric) / CAST($6 AS numeric)), div($7, $8), mod($9, $10), ($11 % $12), ($13 % $14), round(2.5::numeric), round(2.5::float8), round(0.5::float8), round($15::numeric, $16), trunc($17), ceil($18), floor($19)
args: 19
SELECT round("t"."x"::numeric, $1) FROM "t" AS "t"
args: 1
SELECT ($1 / NULLIF("t"."divisor", $2)) FROM "t" AS "t"
args: 2
Example (S11_05_precision)

### 11.5 Float and bigint precision

show(sel().Exprs(
	sqlb.BoolExpr(sqlb.EqExpr(sqlb.Add(sqlb.Literal[float64]("0.1::float8"), sqlb.Literal[float64]("0.2::float8")), sqlb.Literal[float64]("0.3::float8"))),
	sqlb.BoolExpr(sqlb.EqExpr(sqlb.Add(sqlb.Literal[float64]("0.1::numeric"), sqlb.Literal[float64]("0.2")), sqlb.Literal[float64]("0.3"))),
	sqlb.Literal[int64]("9007199254740993::float8::bigint"),
	sqlb.BoolExpr(sqlb.EqExpr(sqlb.Literal[float64]("'NaN'::float8"), sqlb.Literal[float64]("'NaN'::float8"))),
	sqlb.BoolExpr(sqlb.GreaterExpr(sqlb.Literal[float64]("'NaN'::numeric"), sqlb.Literal[float64]("1e30::numeric"))),
).Build())
show(sel().Exprs(sqlb.Add(sqlb.Param[int32](2147483647), sqlb.Param[int32](1))).Build())
show(sel().Exprs(sqlb.Add(sqlb.Cast[int64](sqlb.Param[int32](2147483647), "bigint"), sqlb.Param[int64](1))).Build())
Output:
SELECT (0.1::float8 + 0.2::float8) = 0.3::float8, (0.1::numeric + 0.2) = 0.3, 9007199254740993::float8::bigint, 'NaN'::float8 = 'NaN'::float8, 'NaN'::numeric > 1e30::numeric
SELECT ($1 + $2)
args: 2
SELECT (CAST($1 AS bigint) + $2)
args: 2
Example (S11_06_splitMoney)

### 11.6 Splitting money so the parts sum exactly

g, part := pg.GenerateSeries(1, 3, 0, "g")
inner := sel().Exprs(sqlb.As(part, "part"), sqlb.As(pg.Round(sqlb.Div(sqlb.Literal[float64]("100.00"), sqlb.Param(3.0)), 2), "share"),
	sqlb.As(sqlb.Literal[float64]("100.00"), "total"), sqlb.As(sqlb.Param[int32](3), "n")).From(g)
s := sqlb.Derived(inner, "s")
sPart, share, total, n := ref[int64](s, "part"), ref[float64](s, "share"), ref[float64](s, "total"), ref[int32](s, "n")
remainder := sqlb.Case[float64]().When(sqlb.Eq(sPart, 1), sqlb.Sub(total, sqlb.Mul(share, sqlb.Cast[float64](n, "numeric")))).Else(sqlb.Param(0.0))
show(sel().Exprs(sPart, sqlb.As(sqlb.Add(share, remainder), "adjusted")).From(s).Build())
Output:
SELECT "s"."part", ("s"."share" + CASE WHEN "s"."part" = $1 THEN ("s"."total" - ("s"."share" * CAST("s"."n" AS numeric))) ELSE $2 END) AS "adjusted" FROM (SELECT "g"."n" AS "part", round((100.00 / $3)::numeric, $4) AS "share", 100.00 AS "total", $5 AS "n" FROM generate_series(CAST($6 AS bigint), CAST($7 AS bigint)) AS "g" ("n")) AS "s"
args: 7
Example (S11_07_regex)

### 11.7 Regex: scalar match, global replace, backrefs, split, count

t := tbl("t", "t")
s := sqlb.Col[string](t, "s")
show(sel().Exprs(
	sqlb.As(pg.Subscript(pg.RegexpMatch(s, `(\d+)-(\d+)`), 1), "first_group"),
	sqlb.As(pg.RegexpReplace(s, `(\w+)@(\w+)`, `\2 at \1`, "g"), "swapped"),
	sqlb.As(pg.RegexpReplace(s, `\s+`, " ", "g"), "squeezed"),
	sqlb.As(pg.RegexpSplitToArray(s, `\s*,\s*`), "csv_parts"),
	pg.RegexpCount(s, `\d`),
	pg.RegexpInstr(s, "@"), pg.RegexpSubstr(s, `[a-z]+@[a-z.]+`),
	sqlb.BoolExpr(pg.RegexI(s, "^hello")),
	sqlb.As(pg.SubstringRegex(s, `#(\d+)`), "issue_number"),
).From(t).Build())
Output:
SELECT regexp_match("t"."s", $1)[$2] AS "first_group", regexp_replace("t"."s", $3, $4, $5) AS "swapped", regexp_replace("t"."s", $6, $7, $8) AS "squeezed", regexp_split_to_array("t"."s", $9) AS "csv_parts", regexp_count("t"."s", $10), regexp_instr("t"."s", $11), regexp_substr("t"."s", $12), ("t"."s" ~* $13), substring("t"."s" FROM $14) AS "issue_number" FROM "t" AS "t"
args: 14
Example (S11_08_naturalSort)

### 11.8 Natural (version-aware) sort

f := tbl("files", "files")
name := sqlb.Col[string](f, "name")
numbered := pg.WithOrdinality(pg.FuncSource(sqlb.Fn[string]("regexp_split_to_table", name, sqlb.Param(`(?<=\D)(?=\d)|(?<=\d)(?=\D)`)), "p", "part", "i"))
pPart, pI := ref[string](numbered, "part"), ref[int64](numbered, "i")
padded := sqlb.Case[string]().When(pg.Regex(pPart, `^\d+$`), pg.LPad(pPart, 20, "0")).Else(pPart)
key := sel().Exprs(pg.ArrayAgg(padded, pg.OrderWithin(asc(pI)))).From(numbered)
show(sel().Exprs(name).From(f).Order(asc(sqlb.Subquery[[]string](key))).Build())
r := tbl("releases", "releases")
version := sqlb.Col[string](r, "version")
show(sel().Exprs(version).From(r).Order(asc(sqlb.Cast[[]int32](pg.StringToArray(version, "."), "int[]"))).Build())
Output:
SELECT "files"."name" FROM "files" AS "files" ORDER BY (SELECT array_agg(CASE WHEN ("p"."part" ~ $1) THEN lpad("p"."part", $2, $3) ELSE "p"."part" END ORDER BY "p"."i" ASC) FROM regexp_split_to_table("files"."name", $4) WITH ORDINALITY AS "p" ("part", "i")) ASC
args: 4
SELECT "releases"."version" FROM "releases" AS "releases" ORDER BY CAST(string_to_array("releases"."version", $1) AS int[]) ASC
args: 1
Example (S11_09_collation)

### 11.9 Collation: byte order, case-insensitive, and speed CREATE COLLATION and CREATE TABLE are DDL. The two comparisons render.

t := tbl("t", "t")
name := sqlb.Col[string](t, "name")
show(sel().Exprs(name).From(t).Order(asc(pg.Collate(name, "C"))).Build())
show(sel().Exprs(sqlb.BoolExpr(sqlb.LessExpr(sqlb.Param("a"), pg.Collate(sqlb.Param("B"), "C"))), sqlb.BoolExpr(sqlb.LessExpr(sqlb.Param("a"), pg.Collate(sqlb.Param("B"), "en_US")))).Build())
Output:
SELECT "t"."name" FROM "t" AS "t" ORDER BY "t"."name" COLLATE "C" ASC
SELECT $1 < $2 COLLATE "C", $3 < $4 COLLATE "en_US"
args: 4
Example (S11_10_stringFunctions)

### 11.10 String functions that surprise

show(sel().Exprs(
	pg.Left(sqlb.Param("abcdef"), -2),
	pg.ConcatOp(sqlb.Param("a"), sqlb.Literal[string]("NULL")),
	pg.ConcatWs(", ", sqlb.Param("a"), sqlb.Literal[string]("NULL"), sqlb.Param("b")),
	pg.Format("%I = %L (%s)", sqlb.Param("col name"), sqlb.Param("it's"), sqlb.Param(42)),
	pg.Length(sqlb.Param("héllo")), pg.OctetLength(sqlb.Param("héllo")),
	sqlb.BoolExpr(sqlb.EqExpr(pg.Normalize(sqlb.Param("é"), pg.NFD), sqlb.Param("é"))),
	sqlb.BoolExpr(sqlb.Like(sqlb.Param("abc"), `a\_c`)),
	sqlb.BoolExpr(pg.SimilarTo(sqlb.Param("a.b"), "a.b")),
	pg.SplitPart(sqlb.Param("a-b-c"), "-", -1),
	sqlb.Cast[int32](sqlb.AssertNotNull(sqlb.NullIf(pg.Trim(sqlb.Param(" 42 ")), sqlb.Param(""))), "int"),
	pg.QuoteIdent(sqlb.Param("weird col")), pg.QuoteLiteral(sqlb.Param("a'b")),
).Build())
Output:
SELECT left($1, $2), ($3 || NULL), concat_ws($4, $5, NULL, $6), format($7, $8, $9, $10), length($11), octet_length($12), normalize($13, NFD) = $14, $15 LIKE $16, ($17 SIMILAR TO $18), split_part($19, $20, $21), CAST(NULLIF(btrim($22), $23) AS int), quote_ident($24), quote_literal($25)
args: 25
Example (S11_11_rowValuesAndSetOps)

### 11.11 Row-value comparison and set-operation quirks

show(sel().Exprs(sqlb.BoolExpr(sqlb.RowLess(sqlb.Row(sqlb.Param(1), sqlb.Param("b")), sqlb.Row(sqlb.Param(1), sqlb.Param("c")))),
	sqlb.BoolExpr(sqlb.RowEq(sqlb.Row(sqlb.Param(1), sqlb.Literal[string]("NULL")), sqlb.Row(sqlb.Param(1), sqlb.Literal[string]("NULL"))))).Build())
a, b, c := tbl("a", "a"), tbl("b", "b"), tbl("c", "c")
star := func(t sqlb.Table) sqlb.SQLer { return sqlb.Col[[]byte](t, "*") }
show(pg.ExceptAll(sel().Exprs(star(a)).From(a), sel().Exprs(star(b)).From(b)).Build())
show(sel().Exprs(star(a)).From(a).Intersect(sel().Exprs(star(b)).From(b)).Union(sel().Exprs(star(c)).From(c)).Build())
v := pg.Values("v", []string{"id", "name"}, []sqlb.SQLer{sqlb.Cast[int64](sqlb.Param(1), "bigint"), sqlb.Cast[*string](sqlb.Literal[*string]("NULL"), "text")}, []sqlb.SQLer{sqlb.Param(2), sqlb.Param("x")})
show(sel().Exprs(ref[int64](v, "id"), ref[*string](v, "name")).From(v).Build())
Output:
SELECT ($1, $2) < ($3, $4), ($5, NULL) = ($6, NULL)
args: 6
SELECT "a"."*" FROM "a" AS "a" EXCEPT ALL SELECT "b"."*" FROM "b" AS "b"
SELECT "a"."*" FROM "a" AS "a" INTERSECT SELECT "b"."*" FROM "b" AS "b" UNION SELECT "c"."*" FROM "c" AS "c"
SELECT "v"."id", "v"."name" FROM (VALUES (CAST($1 AS bigint), CAST(NULL AS text)), ($2, $3)) AS "v" ("id", "name")
args: 3
Example (S11_12_enumOrder)

### 11.12 Enum caveats ALTER TYPE, CREATE TYPE and ALTER TABLE are DDL. The ORDER BY renders and sorts by declaration order, which the builder cannot know or check.

items := tbl("items", "items")
status := sqlb.Col[string](items, "status")
show(sel().Exprs(status).From(items).Order(asc(status)).Build())
Output:
SELECT "items"."status" FROM "items" AS "items" ORDER BY "items"."status" ASC
Example (S12_01_jobQueue)

### 12.1 Job queue with SKIP LOCKED CREATE INDEX is DDL. The claim statement renders: a locked CTE, then UPDATE ... FROM it.

j := tbl("jobs", "j")
jID, status, runAt, prio := sqlb.Col[int64](j, "id"), sqlb.Col[string](j, "status"), sqlb.Col[time.Time](j, "run_at"), sqlb.Col[int32](j, "priority")
lockedBy, lockedAt, attempts := sqlb.Col[string](j, "locked_by"), sqlb.Col[time.Time](j, "locked_at"), sqlb.Col[int32](j, "attempts")
next := sel().Exprs(jID).From(j).Where(sqlb.Eq(status, "queued"), sqlb.LessEqExpr(runAt, pg.Now())).
	Order(desc(prio), asc(runAt)).Limit(10).Clause(pg.ForUpdateSkipLocked())
nextT := tbl("next", "next")
show(sqlb.UpdateReturning(sqlb.NewUpdate(d).With("next", next).Table(j).
	Set(sqlb.Set(status, "running"), sqlb.Set(lockedBy, "worker-1"), sqlb.SetExpr(lockedAt, pg.Now()), sqlb.SetExpr(attempts, sqlb.Add(attempts, sqlb.Param[int32](1)))).
	Clause(pg.UpdateFrom(nextT)).Where(sqlb.EqExpr(jID, ref[int64](nextT, "id"))), sqlb.Bind(sqlb.Col[[]byte](j, "*"), &sink.j)).Build())
// Recover abandoned jobs.
show(sqlb.NewUpdate(d).Table(j).Set(sqlb.Set(status, "queued")).
	Where(sqlb.Eq(status, "running"), sqlb.LessExpr(lockedAt, pg.SubIntervalExpr(pg.Now(), pg.IntervalLit("10 minutes")))).Build())
Output:
WITH "next" AS (SELECT "j"."id" FROM "jobs" AS "j" WHERE ("j"."status" = $1 AND "j"."run_at" <= now()) ORDER BY "j"."priority" DESC, "j"."run_at" ASC LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "jobs" AS "j" SET "status" = $3, "locked_by" = $4, "locked_at" = now(),
"attempts" = ("j"."attempts" + $5) FROM "next" AS "next" WHERE ("j"."id" = "next"."id") RETURNING "j"."*"
args: 5
UPDATE "jobs" AS "j" SET "status" = $1 WHERE ("j"."status" = $2 AND "j"."locked_at" < (now() - interval '10 minutes'))
args: 2
Example (S12_02_advisoryLocks)

### 12.2 Advisory locks: transaction-scoped, never leaked The DO block is PL/pgSQL. The two lock calls render as FROM-less selects.

show(sel().Exprs(pg.TryAdvisoryXactLock(pg.Hashtext(sqlb.Concat(sqlb.Param("import:"), sqlb.Param("tenant-7"))))).Build())
show(sel().Exprs(pg.AdvisoryXactLock2(42, sqlb.Cast[int32](sqlb.Param(7), "int"))).Build())
Output:
SELECT pg_try_advisory_xact_lock(hashtext(concat(CAST($1 AS text), CAST($2 AS text))))
args: 2
SELECT pg_advisory_xact_lock($1, CAST($2 AS int))
args: 2
Example (S12_03_lockStrength)

### 12.3 Lock strength: FOR UPDATE versus FOR NO KEY UPDATE SET lock_timeout is session state. The three locking selects render.

o := tbl("orders", "o")
oID, oCustomer := sqlb.Col[int64](o, "id"), sqlb.Col[int64](o, "customer_id")
c := tbl("customers", "c")
show(sel().Exprs(oID).From(o).Where(sqlb.Eq(oID, 1)).Clause(pg.ForNoKeyUpdate()).Build())
show(sel().Exprs(oID).From(o).Join(inner(c, sqlb.EqExpr(sqlb.Col[int64](c, "id"), oCustomer))).Where(sqlb.Eq(oID, 1)).Clause(pg.ForUpdate(pg.Of(o))).Build())
show(sel().Exprs(oID).From(o).Where(sqlb.Eq(oID, 1)).Clause(pg.ForUpdate(pg.NoWait())).Build())
Output:
SELECT "o"."id" FROM "orders" AS "o" WHERE ("o"."id" = $1) FOR NO KEY UPDATE
args: 1
SELECT "o"."id" FROM "orders" AS "o" INNER JOIN "customers" AS "c" ON ("c"."id" = "o"."customer_id") WHERE ("o"."id" = $1) FOR UPDATE OF "o"
args: 1
SELECT "o"."id" FROM "orders" AS "o" WHERE ("o"."id" = $1) FOR UPDATE NOWAIT
args: 1
Example (S12_04_optimisticXmin)

### 12.4 Optimistic concurrency with xmin

docs := tbl("docs", "docs")
id, title, xmin := sqlb.Col[int64](docs, "id"), sqlb.Col[string](docs, "title"), sqlb.Col[string](docs, "xmin")
show(sel().Exprs(id, title, sqlb.As(sqlb.Cast[string](xmin, "text"), "version")).From(docs).Where(sqlb.Eq(id, 1)).Build())
show(sqlb.UpdateReturning(sqlb.NewUpdate(d).Table(docs).Set(sqlb.Set(title, "New")).Where(sqlb.Eq(id, 1), sqlb.Eq(sqlb.Cast[string](xmin, "text"), "4711")), sqlb.Bind(sqlb.As(sqlb.Cast[string](xmin, "text"), "new_version"), &sink.s)).Build())
Output:
SELECT "docs"."id", "docs"."title", CAST("docs"."xmin" AS text) AS "version" FROM "docs" AS "docs" WHERE ("docs"."id" = $1)
args: 1
UPDATE "docs" AS "docs" SET "title" = $1 WHERE ("docs"."id" = $2 AND CAST("docs"."xmin" AS text) = $3) RETURNING CAST("docs"."xmin" AS text) AS "new_version"
args: 3
Example (S12_05_gaplessCounter)

### 12.5 Gapless per-tenant sequence numbers

c := tbl("invoice_counters", "c")
tenant, last := sqlb.Col[int64](c, "tenant_id"), sqlb.Col[int64](c, "last_no")
show(sqlb.InsertReturning(sqlb.NewInsert(d).Into(c).Row(sqlb.Set(tenant, 7), sqlb.Set(last, 1)).
	Clause(pg.OnConflict(tenant).DoUpdate(sqlb.SetExpr(last, sqlb.Add(last, sqlb.Param[int64](1)))).Clause()), sqlb.Bind(last, &sink.i64)).Build())
Output:
INSERT INTO "invoice_counters" AS "c" ("tenant_id", "last_no") VALUES ($1, $2) ON CONFLICT ("tenant_id") DO UPDATE SET "last_no" = ("c"."last_no" + $3) RETURNING "c"."last_no"
args: 3
Example (S12_08_maxNPerParent)

### 12.8 Enforcing a "max N per parent" invariant BEGIN and COMMIT are transaction control. The lock call and the guarded INSERT render.

tm := tbl("team_members", "team_members")
team, user := sqlb.Col[int64](tm, "team_id"), sqlb.Col[int64](tm, "user_id")
show(sel().Exprs(sqlb.Fn[[]byte]("pg_advisory_xact_lock", pg.Hashtext(sqlb.Param("team-members")), sqlb.Param[int64](7))).Build())
count := sel().Exprs(sqlb.Count()).From(tm).Where(sqlb.Eq(team, 7))
show(sqlb.NewInsert(d).Into(tm).Columns(team, user).
	Select(sel().Exprs(sqlb.Param[int64](7), sqlb.Param[int64](99)).Where(sqlb.Less(sqlb.AssertNotNull(sqlb.Subquery[int64](count)), 5))).Build())
Output:
SELECT pg_advisory_xact_lock(hashtext($1), $2)
args: 2
INSERT INTO "team_members" AS "team_members" ("team_id", "user_id")
SELECT $1, $2 WHERE ((SELECT count(*) FROM "team_members" AS "team_members" WHERE ("team_members"."team_id" = $3)) < $4)
args: 4
Example (S12_09_blockingTree)

### 12.9 Blocking tree and how to unblock

blocked, blocking := tbl("pg_stat_activity", "blocked"), tbl("pg_stat_activity", "blocking")
bPid, bStart, bQuery := sqlb.Col[int32](blocked, "pid"), sqlb.Col[time.Time](blocked, "query_start"), sqlb.Col[string](blocked, "query")
kPid, kState, kQuery := sqlb.Col[int32](blocking, "pid"), sqlb.Col[string](blocking, "state"), sqlb.Col[string](blocking, "query")
pids := pg.FuncSource(sqlb.Fn[int32]("unnest", pg.BlockingPids(bPid)), "b", "pid")
show(sel().Exprs(sqlb.As(bPid, "blocked_pid"), sqlb.As(pg.Diff(pg.Now(), bStart), "waiting_for"), sqlb.As(pg.Left(bQuery, 80), "blocked_query"),
	sqlb.As(kPid, "blocking_pid"), sqlb.As(kState, "blocking_state"), sqlb.As(pg.Left(kQuery, 80), "blocking_query")).
	From(blocked).Join(sqlb.Join{Type: sqlb.JoinInner, Source: pg.Lateral(pids), On: []sqlb.Predicate{sqlb.True()}},
	inner(blocking, sqlb.EqExpr(kPid, ref[int32](pids, "pid")))).Build())
show(sel().Exprs(pg.CancelBackend(sqlb.Param[int32](4242))).Build())
act := tbl("pg_stat_activity", "pg_stat_activity")
pid, state, change, query := sqlb.Col[int32](act, "pid"), sqlb.Col[string](act, "state"), sqlb.Col[time.Time](act, "state_change"), sqlb.Col[string](act, "query")
show(sel().Exprs(pid, sqlb.As(pg.Diff(pg.Now(), change), "idle_for"), pg.Left(query, 80)).From(act).
	Where(sqlb.Eq(state, "idle in transaction"), sqlb.GreaterExpr(pg.Diff(pg.Now(), change), pg.IntervalLit("5 minutes"))).Build())
Output:
SELECT "blocked"."pid" AS "blocked_pid", (now() - "blocked"."query_start") AS "waiting_for", left("blocked"."query", $1) AS "blocked_query", "blocking"."pid" AS "blocking_pid", "blocking"."state" AS "blocking_state", left("blocking"."query", $2) AS "blocking_query" FROM "pg_stat_activity" AS "blocked" INNER JOIN LATERAL unnest(pg_blocking_pids("blocked"."pid")) AS "b" ("pid") ON (TRUE) INNER JOIN "pg_stat_activity" AS "blocking" ON ("blocking"."pid" = "b"."pid")
args: 2
SELECT pg_cancel_backend($1)
args: 1
SELECT "pg_stat_activity"."pid", (now() - "pg_stat_activity"."state_change") AS "idle_for", left("pg_stat_activity"."query", $1) FROM "pg_stat_activity" AS "pg_stat_activity" WHERE ("pg_stat_activity"."state" = $2 AND (now() - "pg_stat_activity"."state_change") > interval '5 minutes')
args: 2
Example (S13_03_partitionPruning)

### 13.3 Partition pruning only works with plannable predicates

ev := tbl("events", "e")
created, tenant := sqlb.Col[time.Time](ev, "created_at"), sqlb.Col[int64](ev, "tenant_id")
show(sel().Exprs(created).From(ev).Where(sqlb.GreaterEqExpr(created, pg.SubIntervalExpr(pg.Now(), pg.IntervalLit("1 day")))).Build())
show(sel().Exprs(created).From(ev).Where(sqlb.GreaterEq(created, t0)).Build())
show(sel().Exprs(created).From(ev).Where(sqlb.EqExpr(sqlb.Cast[time.Time](created, "date"), pg.CurrentDate())).Build())
t := tbl("tenants", "t")
show(sel().Exprs(created).From(ev).Join(inner(t, sqlb.EqExpr(sqlb.Col[int64](t, "id"), tenant))).Where(sqlb.Eq(sqlb.Col[string](t, "slug"), "x")).Build())
Output:
SELECT "e"."created_at" FROM "events" AS "e" WHERE ("e"."created_at" >= (now() - interval '1 day'))
SELECT "e"."created_at" FROM "events" AS "e" WHERE ("e"."created_at" >= $1)
args: 1
SELECT "e"."created_at" FROM "events" AS "e" WHERE (CAST("e"."created_at" AS date) = current_date)
SELECT "e"."created_at" FROM "events" AS "e" INNER JOIN "tenants" AS "t" ON ("t"."id" = "e"."tenant_id") WHERE ("t"."slug" = $1)
args: 1
Example (S13_08_invalidIndexes)

### 13.8 CREATE INDEX CONCURRENTLY: invalid leftovers (the catalog query)

idx := tbl("pg_index", "pg_index")
show(sel().Exprs(sqlb.Cast[string](sqlb.Col[int64](idx, "indexrelid"), "regclass")).From(idx).
	Where(sqlb.Not(sqlb.PredicateOf(sqlb.Col[bool](idx, "indisvalid").SQL))).Build())
Output:
SELECT CAST("pg_index"."indexrelid" AS regclass) FROM "pg_index" AS "pg_index" WHERE (NOT ("pg_index"."indisvalid"))
Example (S14_07_functionCalls)

### 14.7 Polymorphic, variadic, table-returning, named arguments (the calls)

top := pg.FuncSource(sqlb.Fn[[]byte]("top_customers", sqlb.Param(5), sqlb.Param("EU"), sqlb.Param("US")), "tc", "customer_id", "revenue")
show(sel().Exprs(ref[int64](top, "customer_id"), ref[float64](top, "revenue")).From(top).Build())
variadic := pg.FuncSource(sqlb.Fn[[]byte]("top_customers", sqlb.Param(5), pg.Variadic(sqlb.Param([]string{"EU"}))), "tc", "customer_id", "revenue")
show(sel().Exprs(ref[int64](variadic, "customer_id")).From(variadic).Build())
show(sel().Exprs(sqlb.Fn[int64]("count_where", pg.Named("p_schema", sqlb.Param("public")), pg.Named("p_table", sqlb.Param("users")), pg.Named("p_col", sqlb.Param("plan")), pg.Named("p_val", sqlb.Param("pro")))).Build())
Output:
SELECT "tc"."customer_id", "tc"."revenue" FROM top_customers($1, $2, $3) AS "tc" ("customer_id", "revenue")
args: 3
SELECT "tc"."customer_id" FROM top_customers($1, VARIADIC $2) AS "tc" ("customer_id", "revenue")
args: 2
SELECT count_where("p_schema" => $1, "p_table" => $2, "p_col" => $3, "p_val" => $4)
args: 4
Example (S15_02_columnMasking)

### 15.2 Column masking through a view (the masking expression)

u := tbl("users", "users")
id, email, ssn := sqlb.Col[int64](u, "id"), sqlb.Col[string](u, "email"), sqlb.Col[string](u, "ssn")
canSee := sqlb.PredicateOf(pg.HasRole(pg.CurrentUser(), "pii_reader", "member").SQL)
show(sel().Exprs(id, email, sqlb.As(sqlb.Case[string]().When(canSee, ssn).Else(pg.ConcatOp(sqlb.Param("***-**-"), pg.Right(ssn, 4))), "ssn")).From(u).Build())
Output:
SELECT "users"."id", "users"."email", CASE WHEN pg_has_role(current_user, $1, $2) THEN "users"."ssn" ELSE ($3 || right("users"."ssn", $4)) END AS "ssn" FROM "users" AS "users"
args: 4
Example (S16_01_keysetPagination)

### 16.1 Keyset pagination

p := tbl("posts", "posts")
id, created, title := sqlb.Col[int64](p, "id"), sqlb.Col[time.Time](p, "created_at"), sqlb.Col[string](p, "title")
show(sel().Exprs(id, created, title).From(p).
	Where(sqlb.RowLess(sqlb.Row(created, id), sqlb.Row(sqlb.Param(t1), sqlb.Param[int64](900)))).
	Order(desc(created), desc(id)).Limit(20).Build())
Output:
SELECT "posts"."id", "posts"."created_at", "posts"."title" FROM "posts" AS "posts" WHERE (("posts"."created_at", "posts"."id") < ($1, $2)) ORDER BY "posts"."created_at" DESC, "posts"."id" DESC LIMIT $3
args: 3
Example (S16_05_approximateCount)

### 16.5 Fast approximate count The count_estimate function is PL/pgSQL. The catalog estimate renders.

pc := tbl("pg_class", "pg_class")
show(sel().Exprs(sqlb.As(sqlb.Cast[int64](sqlb.Col[float64](pc, "reltuples"), "bigint"), "estimate")).From(pc).
	Where(sqlb.EqExpr(sqlb.Col[int64](pc, "oid"), pg.Regclass("orders"))).Build())
Output:
SELECT CAST("pg_class"."reltuples" AS bigint) AS "estimate" FROM "pg_class" AS "pg_class" WHERE ("pg_class"."oid" = 'orders'::regclass)
Example (S16_06_looseIndexScan)

### 16.6 Loose index scan (DISTINCT on a low-cardinality indexed column)

big := tbl("big", "big")
col := sqlb.Col[string](big, "col")
t := tbl("t", "t")
tCol := ref[*string](t, "col")
seed := sel().Exprs(sqlb.As(sqlb.Min(col), "col")).From(big)
next := sel().Exprs(sqlb.Min(col)).From(big).Where(sqlb.GreaterExpr(col, sqlb.AssertNotNull(tCol)))
step := sel().Exprs(sqlb.Subquery[string](next)).From(t).Where(sqlb.IsNotNull(tCol))
show(sel().WithRecursive("t", seed, step).Exprs(tCol).From(t).Where(sqlb.IsNotNull(tCol)).Build())
Output:
WITH RECURSIVE "t" AS (SELECT min("big"."col") AS "col" FROM "big" AS "big" UNION ALL SELECT (SELECT min("big"."col") FROM "big" AS "big" WHERE ("big"."col" > "t"."col")) FROM "t" AS "t" WHERE ("t"."col" IS NOT NULL))
SELECT "t"."col" FROM "t" AS "t" WHERE ("t"."col" IS NOT NULL)
Example (S16_07_queryRewrites)

### 16.7 Query rewrites that change the plan

o := tbl("orders", "orders")
customer, rep := sqlb.Col[int64](o, "customer_id"), sqlb.Col[int64](o, "sales_rep_id")
star := sqlb.Col[[]byte](o, "*")
show(sel().Exprs(star).From(o).Where(sqlb.Eq(customer, 5)).Union(sel().Exprs(star).From(o).Where(sqlb.Eq(rep, 5))).Build())
show(sel().Exprs(sqlb.BoolExpr(sqlb.Exists(sel().Exprs(sqlb.Param(1)).From(o).Where(sqlb.Eq(customer, 5))))).Build())
ev := tbl("events", "events")
ts, created, tenant := sqlb.Col[time.Time](ev, "ts"), sqlb.Col[time.Time](ev, "created_at"), sqlb.Col[int64](ev, "tenant_id")
recent := sel().Exprs(sqlb.Col[[]byte](ev, "*")).From(ev).Where(sqlb.GreaterExpr(ts, pg.SubIntervalExpr(pg.Now(), pg.IntervalLit("1 hour"))))
r := tbl("recent", "r")
show(sel().WithCTE(sqlb.CTE{Name: "recent", Body: recent, Prefix: pg.Materialized(true)}).Exprs(sqlb.Col[[]byte](r, "*")).From(r).Build())
show(sel().WithCTE(sqlb.CTE{Name: "recent", Body: recent, Prefix: pg.Materialized(false)}).Exprs(sqlb.Col[[]byte](r, "*")).From(r).Build())
show(sel().Exprs(created).From(ev).Where(sqlb.GreaterExpr(created, pg.SubIntervalExpr(pg.Now(), pg.IntervalLit("1 day")))).Build())
show(sel().Exprs(created).From(ev).Where(sqlb.GreaterExpr(created, pg.SubIntervalExpr(pg.ClockTimestamp(), pg.IntervalLit("1 day")))).Build())
show(sel().Exprs(created).From(ev).Where(sqlb.Eq(tenant, 7)).Order(desc(created)).Limit(10).Build())
Output:
SELECT "orders"."*" FROM "orders" AS "orders" WHERE ("orders"."customer_id" = $1) UNION SELECT "orders"."*" FROM "orders" AS "orders" WHERE ("orders"."sales_rep_id" = $2)
args: 2
SELECT EXISTS (SELECT $1 FROM "orders" AS "orders" WHERE ("orders"."customer_id" = $2))
args: 2
WITH "recent" AS MATERIALIZED (SELECT "events"."*" FROM "events" AS "events" WHERE ("events"."ts" > (now() - interval '1 hour')))
SELECT "r"."*" FROM "recent" AS "r"
WITH "recent" AS NOT MATERIALIZED (SELECT "events"."*" FROM "events" AS "events" WHERE ("events"."ts" > (now() - interval '1 hour')))
SELECT "r"."*" FROM "recent" AS "r"
SELECT "events"."created_at" FROM "events" AS "events" WHERE ("events"."created_at" > (now() - interval '1 day'))
SELECT "events"."created_at" FROM "events" AS "events" WHERE ("events"."created_at" > (clock_timestamp() - interval '1 day'))
SELECT "events"."created_at" FROM "events" AS "events" WHERE ("events"."tenant_id" = $1) ORDER BY "events"."created_at" DESC LIMIT $2
args: 2
Example (S16_08_pgStatStatements)

### 16.8 pg_stat_statements: where the time goes

s := tbl("pg_stat_statements", "pg_stat_statements")
calls, total, mean, rows := sqlb.Col[int64](s, "calls"), sqlb.Col[float64](s, "total_exec_time"), sqlb.Col[float64](s, "mean_exec_time"), sqlb.Col[int64](s, "rows")
hit, read, query := sqlb.Col[float64](s, "shared_blks_hit"), sqlb.Col[float64](s, "shared_blks_read"), sqlb.Col[string](s, "query")
show(sel().Exprs(calls, sqlb.As(pg.Round(total, 1), "total_ms"), sqlb.As(pg.Round(mean, 2), "mean_ms"), rows,
	sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), hit), sqlb.AssertNotNull(sqlb.NullIf(sqlb.Add(hit, read), sqlb.Param(0.0)))), 1), "cache_hit_pct"),
	sqlb.As(pg.Left(query, 120), "query")).From(s).Order(desc(total)).Limit(20).Build())
Output:
SELECT "pg_stat_statements"."calls", round("pg_stat_statements"."total_exec_time"::numeric, $1) AS "total_ms", round("pg_stat_statements"."mean_exec_time"::numeric, $2) AS "mean_ms", "pg_stat_statements"."rows", round((($3 * "pg_stat_statements"."shared_blks_hit") / NULLIF(("pg_stat_statements"."shared_blks_hit" + "pg_stat_statements"."shared_blks_read"), $4))::numeric, $5) AS "cache_hit_pct", left("pg_stat_statements"."query", $6) AS "query" FROM "pg_stat_statements" AS "pg_stat_statements" ORDER BY "pg_stat_statements"."total_exec_time" DESC LIMIT $7
args: 7
Example (S16_09_toast)

### 16.9 TOAST and column storage The ALTER statements are DDL. The size probe renders.

ev := tbl("events", "events")
payload, id := sqlb.Col[[]byte](ev, "payload"), sqlb.Col[int64](ev, "id")
show(sel().Exprs(sqlb.As(pg.ColumnSize(payload), "stored_bytes"), sqlb.As(pg.OctetLength(sqlb.Cast[string](payload, "text")), "raw_bytes")).
	From(ev).Where(sqlb.Eq(id, 1)).Build())
Output:
SELECT pg_column_size("events"."payload") AS "stored_bytes", octet_length(CAST("events"."payload" AS text)) AS "raw_bytes" FROM "events" AS "events" WHERE ("events"."id" = $1)
args: 1
Example (S16_10_hotUpdates)

### 16.10 HOT updates and fillfactor The ALTER statements are DDL. The HOT ratio probe renders.

t := tbl("pg_stat_user_tables", "pg_stat_user_tables")
upd, hot, rel := sqlb.Col[float64](t, "n_tup_upd"), sqlb.Col[float64](t, "n_tup_hot_upd"), sqlb.Col[string](t, "relname")
show(sel().Exprs(upd, hot, sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), hot), sqlb.AssertNotNull(sqlb.NullIf(upd, sqlb.Param(0.0)))), 1), "hot_pct")).
	From(t).Where(sqlb.Eq(rel, "counters")).Build())
Output:
SELECT "pg_stat_user_tables"."n_tup_upd", "pg_stat_user_tables"."n_tup_hot_upd", round((($1 * "pg_stat_user_tables"."n_tup_hot_upd") / NULLIF("pg_stat_user_tables"."n_tup_upd", $2))::numeric, $3) AS "hot_pct" FROM "pg_stat_user_tables" AS "pg_stat_user_tables" WHERE ("pg_stat_user_tables"."relname" = $4)
args: 4
Example (S16_11_tablesample)

### 16.11 Sampling instead of ORDER BY random()

big := tbl("big", "big")
star := sqlb.Col[[]byte](big, "*")
show(sel().Exprs(star).From(big).Clause(pg.TableSample(pg.SampleSystem, 0.1, 42)).Build())
show(sel().Exprs(star).From(big).Clause(pg.TableSample(pg.SampleBernoulli, 1, -1)).Build())
show(sel().Exprs(star).From(big).Clause(pg.TableSample(pg.SampleMethod("SYSTEM_ROWS"), 100, -1)).Build())
Output:
SELECT "big"."*" FROM "big" AS "big" TABLESAMPLE SYSTEM (0.1) REPEATABLE (42)
SELECT "big"."*" FROM "big" AS "big" TABLESAMPLE BERNOULLI (1)
SELECT "big"."*" FROM "big" AS "big" TABLESAMPLE SYSTEM_ROWS (100)
Example (S17_01_tableSizes)

### 17.1 Table sizes with heap, indexes, and TOAST split out

c, n := tbl("pg_class", "c"), tbl("pg_namespace", "n")
oid, relname, toast, reltuples, relkind, relns := sqlb.Col[int64](c, "oid"), sqlb.Col[string](c, "relname"), sqlb.Col[int64](c, "reltoastrelid"), sqlb.Col[float64](c, "reltuples"), sqlb.Col[string](c, "relkind"), sqlb.Col[int64](c, "relnamespace")
nspname, noid := sqlb.Col[string](n, "nspname"), sqlb.Col[int64](n, "oid")
show(sel().Exprs(sqlb.As(pg.ConcatOp(pg.ConcatOp(nspname, sqlb.Param(".")), relname), "relation"),
	sqlb.As(pg.SizePretty(pg.TotalRelationSize(oid)), "total"), sqlb.As(pg.SizePretty(pg.RelationSize(oid)), "heap"),
	sqlb.As(pg.SizePretty(pg.IndexesSize(oid)), "indexes"),
	sqlb.As(pg.SizePretty(sqlb.Case[int64]().When(sqlb.NotEq(toast, 0), pg.TotalRelationSize(toast)).Else(sqlb.Param[int64](0))), "toast"),
	sqlb.As(sqlb.Cast[int64](reltuples, "bigint"), "est_rows")).
	From(c).Join(inner(n, sqlb.EqExpr(noid, relns))).
	Where(sqlb.In(relkind, "r", "m", "p"), sqlb.NotIn(nspname, "pg_catalog", "information_schema")).
	Order(desc(pg.TotalRelationSize(oid))).Limit(25).Build())
Output:
SELECT (("n"."nspname" || $1) || "c"."relname") AS "relation", pg_size_pretty(pg_total_relation_size("c"."oid")) AS "total", pg_size_pretty(pg_relation_size("c"."oid")) AS "heap", pg_size_pretty(pg_indexes_size("c"."oid")) AS "indexes", pg_size_pretty(CASE WHEN "c"."reltoastrelid" <> $2 THEN pg_total_relation_size("c"."reltoastrelid") ELSE $3 END) AS "toast", CAST("c"."reltuples" AS bigint) AS "est_rows" FROM "pg_class" AS "c" INNER JOIN "pg_namespace" AS "n" ON ("n"."oid" = "c"."relnamespace") WHERE ("c"."relkind" IN ($4, $5, $6) AND "n"."nspname" NOT IN ($7, $8)) ORDER BY pg_total_relation_size("c"."oid") DESC LIMIT $9
args: 9
Example (S17_02_deadTuples)

### 17.2 Dead tuples, bloat, and vacuum progress

t := tbl("pg_stat_user_tables", "pg_stat_user_tables")
rel, live, dead := sqlb.Col[string](t, "relname"), sqlb.Col[float64](t, "n_live_tup"), sqlb.Col[float64](t, "n_dead_tup")
show(sel().Exprs(rel, live, dead,
	sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), dead), sqlb.AssertNotNull(sqlb.NullIf(sqlb.Add(live, dead), sqlb.Param(0.0)))), 1), "dead_pct"),
	sqlb.Col[*time.Time](t, "last_autovacuum"), sqlb.Col[*time.Time](t, "last_autoanalyze")).From(t).Order(desc(dead)).Limit(20).Build())
// pgstattuple is an extension function source; pg_stat_progress_vacuum is a view.
st := pg.FuncSource(sqlb.Fn[[]byte]("pgstattuple", sqlb.Param("orders")), "st")
show(sel().Exprs(sqlb.Col[[]byte](sqlb.Ref(st), "*")).From(st).Build())
Output:
SELECT "pg_stat_user_tables"."relname", "pg_stat_user_tables"."n_live_tup", "pg_stat_user_tables"."n_dead_tup", round((($1 * "pg_stat_user_tables"."n_dead_tup") / NULLIF(("pg_stat_user_tables"."n_live_tup" + "pg_stat_user_tables"."n_dead_tup"), $2))::numeric, $3) AS "dead_pct", "pg_stat_user_tables"."last_autovacuum", "pg_stat_user_tables"."last_autoanalyze" FROM "pg_stat_user_tables" AS "pg_stat_user_tables" ORDER BY "pg_stat_user_tables"."n_dead_tup" DESC LIMIT $4
args: 4
SELECT "st"."*" FROM pgstattuple($1) AS "st"
args: 1
Example (S17_03_unindexedForeignKeys)

### 17.3 Foreign keys with no supporting index

c, a, i := tbl("pg_constraint", "c"), tbl("pg_attribute", "a"), tbl("pg_index", "i")
conrelid, conkey, conname, contype := sqlb.Col[int64](c, "conrelid"), sqlb.Col[[]int16](c, "conkey"), sqlb.Col[string](c, "conname"), sqlb.Col[string](c, "contype")
x := pg.WithOrdinality(pg.FuncSource(sqlb.Fn[int16]("unnest", conkey), "x", "attnum", "n"))
xAttnum, xN := ref[int16](x, "attnum"), ref[int64](x, "n")
attname, attnum, attrelid := sqlb.Col[string](a, "attname"), sqlb.Col[int16](a, "attnum"), sqlb.Col[int64](a, "attrelid")
indrelid, indpred, indkey := sqlb.Col[int64](i, "indrelid"), sqlb.Col[*string](i, "indpred"), sqlb.Col[string](i, "indkey")
leading := pg.SliceExprs(sqlb.Cast[[]int16](indkey, "smallint[]"), sqlb.Param(0), sqlb.Sub(pg.Cardinality(conkey), sqlb.Param[int32](1)))
covered := sel().Exprs(sqlb.Param(1)).From(i).Where(sqlb.EqExpr(indrelid, conrelid), sqlb.IsNull(indpred), pg.ArrayContainsExpr(leading, conkey))
show(sel().Exprs(sqlb.As(sqlb.Cast[string](conrelid, "regclass"), "table"),
	sqlb.As(pg.StringAgg(attname, ", ", pg.OrderWithin(asc(xN))), "fk_columns"), conname,
	sqlb.As(pg.SizePretty(pg.RelationSize(conrelid)), "table_size")).
	From(c).Join(crossLateral(x), inner(a, sqlb.EqExpr(attnum, xAttnum), sqlb.EqExpr(attrelid, conrelid))).
	Where(sqlb.Eq(contype, "f"), sqlb.NotExists(covered)).
	GroupBy(conrelid, conname).Order(desc(pg.RelationSize(conrelid))).Build())
Output:
SELECT CAST("c"."conrelid" AS regclass) AS "table", string_agg("a"."attname", $1 ORDER BY "x"."n" ASC) AS "fk_columns", "c"."conname", pg_size_pretty(pg_relation_size("c"."conrelid")) AS "table_size" FROM "pg_constraint" AS "c" CROSS JOIN LATERAL unnest("c"."conkey") WITH ORDINALITY AS "x" ("attnum", "n") INNER JOIN "pg_attribute" AS "a" ON ("a"."attnum" = "x"."attnum" AND "a"."attrelid" = "c"."conrelid") WHERE ("c"."contype" = $2 AND NOT EXISTS (SELECT $3 FROM "pg_index" AS "i" WHERE ("i"."indrelid" = "c"."conrelid" AND "i"."indpred" IS NULL AND ((CAST("i"."indkey" AS smallint[]))[$4:(cardinality("c"."conkey") - $5)] @> "c"."conkey")))) GROUP BY "c"."conrelid", "c"."conname" ORDER BY pg_relation_size("c"."conrelid") DESC
args: 5
Example (S17_04_unusedAndDuplicateIndexes)

### 17.4 Unused and duplicate indexes

s, i := tbl("pg_stat_user_indexes", "s"), tbl("pg_index", "i")
sIdx, sScan := sqlb.Col[int64](s, "indexrelid"), sqlb.Col[int64](s, "idx_scan")
iIdx, unique, primary := sqlb.Col[int64](i, "indexrelid"), sqlb.Col[bool](i, "indisunique"), sqlb.Col[bool](i, "indisprimary")
show(sel().Exprs(sqlb.Col[string](s, "schemaname"), sqlb.Col[string](s, "relname"), sqlb.Col[string](s, "indexrelname"),
	sqlb.As(pg.SizePretty(pg.RelationSize(sIdx)), "size"), sScan).
	From(s).Join(inner(i, sqlb.EqExpr(iIdx, sIdx))).
	Where(sqlb.Eq(sScan, 0), sqlb.Not(sqlb.PredicateOf(unique.SQL)), sqlb.Not(sqlb.PredicateOf(primary.SQL))).
	Order(desc(pg.RelationSize(sIdx))).Build())
idx := tbl("pg_index", "pg_index")
indrelid, indexrelid := sqlb.Col[int64](idx, "indrelid"), sqlb.Col[int64](idx, "indexrelid")
text := func(col string) sqlb.SQLer { return sqlb.Cast[string](sqlb.Col[string](idx, col), "text") }
show(sel().Exprs(sqlb.As(sqlb.Cast[string](indrelid, "regclass"), "table"), sqlb.As(pg.ArrayAgg(sqlb.Cast[string](indexrelid, "regclass")), "duplicates"),
	sqlb.As(pg.SizePretty(sqlb.Sum(pg.RelationSize(indexrelid))), "total_size")).
	From(idx).GroupBy(indrelid, text("indkey"), text("indclass"), text("indoption"),
	sqlb.Coalesce(sqlb.Cast[string](sqlb.Col[*string](idx, "indexprs"), "text"), sqlb.Param("")), sqlb.Coalesce(sqlb.Cast[string](sqlb.Col[*string](idx, "indpred"), "text"), sqlb.Param(""))).
	Having(sqlb.Greater(sqlb.Count(), 1)).Build())
Output:
SELECT "s"."schemaname", "s"."relname", "s"."indexrelname", pg_size_pretty(pg_relation_size("s"."indexrelid")) AS "size", "s"."idx_scan" FROM "pg_stat_user_indexes" AS "s" INNER JOIN "pg_index" AS "i" ON ("i"."indexrelid" = "s"."indexrelid") WHERE ("s"."idx_scan" = $1 AND NOT ("i"."indisunique") AND NOT ("i"."indisprimary")) ORDER BY pg_relation_size("s"."indexrelid") DESC
args: 1
SELECT CAST("pg_index"."indrelid" AS regclass) AS "table", array_agg(CAST("pg_index"."indexrelid" AS regclass)) AS "duplicates", pg_size_pretty(sum(pg_relation_size("pg_index"."indexrelid"))) AS "total_size" FROM "pg_index" AS "pg_index" GROUP BY "pg_index"."indrelid", CAST("pg_index"."indkey" AS text), CAST("pg_index"."indclass" AS text), CAST("pg_index"."indoption" AS text), COALESCE(CAST("pg_index"."indexprs" AS text), $1), COALESCE(CAST("pg_index"."indpred" AS text), $2) HAVING (count(*) > $3)
args: 3
Example (S17_05_sequenceOverflow)

### 17.5 Sequences about to overflow their column

dep, seq, table, col, ps := tbl("pg_depend", "d"), tbl("pg_class", "seq"), tbl("pg_class", "tbl"), tbl("pg_attribute", "col"), tbl("pg_sequences", "ps")
typ := pg.FormatType(sqlb.Col[int64](col, "atttypid"), sqlb.Col[int32](col, "atttypmod"))
colMax := sqlb.Case[int64]().When(sqlb.Eq(typ, "smallint"), sqlb.Param[int64](32767)).When(sqlb.Eq(typ, "integer"), sqlb.Param[int64](2147483647)).Else(sqlb.Param[int64](9223372036854775807))
last := sqlb.Col[int64](ps, "last_value")
show(sel().Exprs(sqlb.As(sqlb.Col[string](seq, "relname"), "sequence"), sqlb.As(sqlb.Col[string](table, "relname"), "table"), sqlb.As(sqlb.Col[string](col, "attname"), "column"),
	sqlb.As(typ, "type"), last, sqlb.As(colMax, "column_max"),
	sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), sqlb.Cast[float64](last, "float8")), sqlb.Cast[float64](colMax, "float8")), 2), "pct_used")).
	From(dep).
	Join(inner(seq, sqlb.EqExpr(sqlb.Col[int64](seq, "oid"), sqlb.Col[int64](dep, "objid")), sqlb.Eq(sqlb.Col[string](seq, "relkind"), "S")),
		inner(table, sqlb.EqExpr(sqlb.Col[int64](table, "oid"), sqlb.Col[int64](dep, "refobjid"))),
		inner(col, sqlb.EqExpr(sqlb.Col[int64](col, "attrelid"), sqlb.Col[int64](dep, "refobjid")), sqlb.EqExpr(sqlb.Col[int32](col, "attnum"), sqlb.Col[int32](dep, "refobjsubid"))),
		inner(ps, sqlb.EqExpr(sqlb.Col[string](ps, "schemaname"), sqlb.Cast[string](sqlb.Cast[string](sqlb.Col[int64](seq, "relnamespace"), "regnamespace"), "text")), sqlb.EqExpr(sqlb.Col[string](ps, "sequencename"), sqlb.Col[string](seq, "relname")))).
	Where(sqlb.In(sqlb.Col[string](dep, "deptype"), "a", "i"), sqlb.IsNotNull(last)).
	Order(desc(sqlb.OutputCol[float64]("pct_used"))).Build())
Output:
SELECT "seq"."relname" AS "sequence", "tbl"."relname" AS "table", "col"."attname" AS "column", format_type("col"."atttypid", "col"."atttypmod") AS "type", "ps"."last_value", CASE WHEN format_type("col"."atttypid", "col"."atttypmod") = $1 THEN $2 WHEN format_type("col"."atttypid", "col"."atttypmod") = $3 THEN $4 ELSE $5 END AS "column_max", round((($6 * CAST("ps"."last_value" AS float8)) / CAST(CASE WHEN format_type("col"."atttypid", "col"."atttypmod") = $7 THEN $8 WHEN format_type("col"."atttypid", "col"."atttypmod") = $9 THEN $10 ELSE $11 END AS float8))::numeric, $12) AS "pct_used" FROM "pg_depend" AS "d" INNER JOIN "pg_class" AS "seq" ON ("seq"."oid" = "d"."objid" AND "seq"."relkind" = $13) INNER JOIN "pg_class" AS "tbl" ON ("tbl"."oid" = "d"."refobjid") INNER JOIN "pg_attribute" AS "col" ON ("col"."attrelid" = "d"."refobjid" AND "col"."attnum" = "d"."refobjsubid") INNER JOIN "pg_sequences" AS "ps" ON ("ps"."schemaname" = CAST(CAST("seq"."relnamespace" AS regnamespace) AS text) AND "ps"."sequencename" = "seq"."relname") WHERE ("d"."deptype" IN ($14, $15) AND "ps"."last_value" IS NOT NULL) ORDER BY "pct_used" DESC
args: 15
Example (S17_06_wraparound)

### 17.6 Transaction ID wraparound distance

db := tbl("pg_database", "pg_database")
frozen := pg.XidAge(sqlb.Col[int64](db, "datfrozenxid"))
show(sel().Exprs(sqlb.Col[string](db, "datname"), sqlb.As(frozen, "xid_age"),
	sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), sqlb.Cast[float64](frozen, "float8")), sqlb.Param(2147483648.0)), 1), "pct_to_wraparound")).
	From(db).Order(desc(sqlb.OutputCol[int64]("xid_age"))).Build())
pc := tbl("pg_class", "pg_class")
show(sel().Exprs(sqlb.Col[string](pc, "relname"), pg.XidAge(sqlb.Col[int64](pc, "relfrozenxid"))).From(pc).
	Where(sqlb.Eq(sqlb.Col[string](pc, "relkind"), "r")).Order(desc(pg.XidAge(sqlb.Col[int64](pc, "relfrozenxid")))).Limit(10).Build())
px := tbl("pg_prepared_xacts", "pg_prepared_xacts")
show(sel().Exprs(sqlb.Col[[]byte](px, "*")).From(px).Build())
Output:
SELECT "pg_database"."datname", age("pg_database"."datfrozenxid") AS "xid_age", round((($1 * CAST(age("pg_database"."datfrozenxid") AS float8)) / $2)::numeric, $3) AS "pct_to_wraparound" FROM "pg_database" AS "pg_database" ORDER BY "xid_age" DESC
args: 3
SELECT "pg_class"."relname", age("pg_class"."relfrozenxid") FROM "pg_class" AS "pg_class" WHERE ("pg_class"."relkind" = $1) ORDER BY age("pg_class"."relfrozenxid") DESC LIMIT $2
args: 2
SELECT "pg_prepared_xacts"."*" FROM "pg_prepared_xacts" AS "pg_prepared_xacts"
Example (S17_07_activity)

### 17.7 What is running, what is idle, what is waiting

a := tbl("pg_stat_activity", "pg_stat_activity")
pid, state, xact, qstart, query := sqlb.Col[int32](a, "pid"), sqlb.Col[string](a, "state"), sqlb.Col[time.Time](a, "xact_start"), sqlb.Col[time.Time](a, "query_start"), sqlb.Col[string](a, "query")
show(sel().Exprs(pid, sqlb.Col[string](a, "usename"), sqlb.Col[string](a, "application_name"), state, sqlb.Col[string](a, "wait_event_type"), sqlb.Col[string](a, "wait_event"),
	sqlb.As(pg.Diff(pg.Now(), xact), "xact_age"), sqlb.As(pg.Diff(pg.Now(), qstart), "query_age"), sqlb.As(pg.Left(query, 100), "query")).
	From(a).Where(sqlb.NotEq(state, "idle"), sqlb.NotEqExpr(pid, pg.BackendPID())).Order(pg.OrderByNulls(xact, sqlb.SortAsc, false)).Build())
show(sel().Exprs(state, sqlb.Count()).From(a).GroupBy(state).Build())
show(sel().Exprs(pg.TerminateBackend(pid)).From(a).Where(sqlb.Eq(sqlb.Col[string](a, "datname"), "app"), sqlb.NotEqExpr(pid, pg.BackendPID())).Build())
Output:
SELECT "pg_stat_activity"."pid", "pg_stat_activity"."usename", "pg_stat_activity"."application_name", "pg_stat_activity"."state", "pg_stat_activity"."wait_event_type", "pg_stat_activity"."wait_event", (now() - "pg_stat_activity"."xact_start") AS "xact_age", (now() - "pg_stat_activity"."query_start") AS "query_age", left("pg_stat_activity"."query", $1) AS "query" FROM "pg_stat_activity" AS "pg_stat_activity" WHERE ("pg_stat_activity"."state" <> $2 AND "pg_stat_activity"."pid" <> pg_backend_pid()) ORDER BY "pg_stat_activity"."xact_start" ASC NULLS LAST
args: 2
SELECT "pg_stat_activity"."state", count(*) FROM "pg_stat_activity" AS "pg_stat_activity" GROUP BY "pg_stat_activity"."state"
SELECT pg_terminate_backend("pg_stat_activity"."pid") FROM "pg_stat_activity" AS "pg_stat_activity" WHERE ("pg_stat_activity"."datname" = $1 AND "pg_stat_activity"."pid" <> pg_backend_pid())
args: 1
Example (S17_08_replicationLag)

### 17.8 Replication lag

r := tbl("pg_stat_replication", "pg_stat_replication")
lsnDiff := pg.WalLsnDiff(pg.CurrentWalLsn(), sqlb.Col[string](r, "replay_lsn"))
show(sel().Exprs(sqlb.Col[string](r, "client_addr"), sqlb.Col[string](r, "state"), sqlb.Col[string](r, "sync_state"), sqlb.As(lsnDiff, "replay_lag_bytes"),
	sqlb.Col[pg.Interval](r, "write_lag"), sqlb.Col[pg.Interval](r, "flush_lag"), sqlb.Col[pg.Interval](r, "replay_lag")).From(r).Build())
show(sel().Exprs(sqlb.As(pg.Diff(pg.Now(), pg.LastXactReplayTimestamp()), "lag")).Build())
s := tbl("pg_replication_slots", "pg_replication_slots")
show(sel().Exprs(sqlb.Col[string](s, "slot_name"), sqlb.Col[bool](s, "active"),
	sqlb.As(pg.SizePretty(pg.WalLsnDiff(pg.CurrentWalLsn(), sqlb.Col[string](s, "restart_lsn"))), "retained_wal")).From(s).Build())
Output:
SELECT "pg_stat_replication"."client_addr", "pg_stat_replication"."state", "pg_stat_replication"."sync_state", pg_wal_lsn_diff(pg_current_wal_lsn(),
"pg_stat_replication"."replay_lsn") AS "replay_lag_bytes", "pg_stat_replication"."write_lag", "pg_stat_replication"."flush_lag", "pg_stat_replication"."replay_lag" FROM "pg_stat_replication" AS "pg_stat_replication"
SELECT (now() - pg_last_xact_replay_timestamp()) AS "lag"
SELECT "pg_replication_slots"."slot_name", "pg_replication_slots"."active", pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
"pg_replication_slots"."restart_lsn")) AS "retained_wal" FROM "pg_replication_slots" AS "pg_replication_slots"
Example (S17_09_columnDependencies)

### 17.9 What depends on this column (why ALTER fails)

dep, rw, v := tbl("pg_depend", "d"), tbl("pg_rewrite", "r"), tbl("pg_class", "v")
att := tbl("pg_attribute", "pg_attribute")
attnum := sel().Exprs(sqlb.Col[int32](att, "attnum")).From(att).
	Where(sqlb.EqExpr(sqlb.Col[int64](att, "attrelid"), pg.Regclass("orders")), sqlb.Eq(sqlb.Col[string](att, "attname"), "total"))
show(sel().Distinct().Exprs(sqlb.As(sqlb.Cast[string](sqlb.Col[int64](v, "oid"), "regclass"), "dependent_view")).From(dep).
	Join(inner(rw, sqlb.EqExpr(sqlb.Col[int64](rw, "oid"), sqlb.Col[int64](dep, "objid"))), inner(v, sqlb.EqExpr(sqlb.Col[int64](v, "oid"), sqlb.Col[int64](rw, "ev_class")))).
	Where(sqlb.EqExpr(sqlb.Col[int64](dep, "refobjid"), pg.Regclass("orders")),
		sqlb.EqExpr(sqlb.Col[int32](dep, "refobjsubid"), sqlb.AssertNotNull(sqlb.Subquery[int32](attnum))),
		sqlb.In(sqlb.Col[string](v, "relkind"), "v", "m")).Build())
Output:
SELECT DISTINCT CAST("v"."oid" AS regclass) AS "dependent_view" FROM "pg_depend" AS "d" INNER JOIN "pg_rewrite" AS "r" ON ("r"."oid" = "d"."objid") INNER JOIN "pg_class" AS "v" ON ("v"."oid" = "r"."ev_class") WHERE ("d"."refobjid" = 'orders'::regclass AND "d"."refobjsubid" = (SELECT "pg_attribute"."attnum" FROM "pg_attribute" AS "pg_attribute" WHERE ("pg_attribute"."attrelid" = 'orders'::regclass AND "pg_attribute"."attname" = $1)) AND "v"."relkind" IN ($2, $3))
args: 3
Example (S17_10_configDrift)

### 17.10 Config drift and cache hit ratio

s := tbl("pg_settings", "pg_settings")
show(sel().Exprs(sqlb.Col[string](s, "name"), sqlb.Col[string](s, "setting"), sqlb.Col[*string](s, "unit"), sqlb.Col[string](s, "source"), sqlb.Col[*string](s, "sourcefile"), sqlb.Col[*int32](s, "sourceline")).
	From(s).Where(sqlb.NotIn(sqlb.Col[string](s, "source"), "default", "override")).Order(asc(sqlb.Col[string](s, "name"))).Build())
f := tbl("pg_file_settings", "pg_file_settings")
show(sel().Exprs(sqlb.Col[[]byte](f, "*")).From(f).Where(sqlb.IsNotNull(sqlb.Col[*string](f, "error"))).Build())
io := tbl("pg_statio_user_tables", "pg_statio_user_tables")
hit, read := sqlb.Col[float64](io, "heap_blks_hit"), sqlb.Col[float64](io, "heap_blks_read")
show(sel().Exprs(sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), sqlb.Sum(hit)), sqlb.AssertNotNull(sqlb.NullIf(sqlb.Sum(sqlb.Add(hit, read)), sqlb.Param(0.0)))), 2), "heap_cache_hit_pct")).From(io).Build())
sio := tbl("pg_stat_io", "pg_stat_io")
reads := sqlb.Col[int64](sio, "reads")
show(sel().Exprs(sqlb.Col[string](sio, "backend_type"), sqlb.Col[string](sio, "object"), sqlb.Col[string](sio, "context"), reads, sqlb.Col[int64](sio, "writes"), sqlb.Col[int64](sio, "extends"), sqlb.Col[int64](sio, "evictions")).
	From(sio).Where(sqlb.Greater(reads, 0)).Order(desc(reads)).Build())
Output:
SELECT "pg_settings"."name", "pg_settings"."setting", "pg_settings"."unit", "pg_settings"."source", "pg_settings"."sourcefile", "pg_settings"."sourceline" FROM "pg_settings" AS "pg_settings" WHERE ("pg_settings"."source" NOT IN ($1, $2)) ORDER BY "pg_settings"."name" ASC
args: 2
SELECT "pg_file_settings"."*" FROM "pg_file_settings" AS "pg_file_settings" WHERE ("pg_file_settings"."error" IS NOT NULL)
SELECT round((($1 * sum("pg_statio_user_tables"."heap_blks_hit")) / NULLIF(sum(("pg_statio_user_tables"."heap_blks_hit" + "pg_statio_user_tables"."heap_blks_read")), $2))::numeric, $3) AS "heap_cache_hit_pct" FROM "pg_statio_user_tables" AS "pg_statio_user_tables"
args: 3
SELECT "pg_stat_io"."backend_type", "pg_stat_io"."object", "pg_stat_io"."context", "pg_stat_io"."reads", "pg_stat_io"."writes", "pg_stat_io"."extends", "pg_stat_io"."evictions" FROM "pg_stat_io" AS "pg_stat_io" WHERE ("pg_stat_io"."reads" > $1) ORDER BY "pg_stat_io"."reads" DESC
args: 1
Example (S18_01_crosstab)

### 18.1 crosstab (tablefunc) CREATE EXTENSION is DDL. crosstab takes its two queries as text, so they are author constants here; the typed column list is pg.RecordSet.

ct := pg.RecordSet(sqlb.Fn[[]byte]("crosstab",
	sqlb.Param("SELECT region, quarter, sum(amount) FROM sales GROUP BY 1, 2 ORDER BY 1, 2"),
	sqlb.Param("SELECT unnest(ARRAY['Q1', 'Q2', 'Q3', 'Q4'])")), "ct",
	pg.ColumnDef{Name: "region", Type: "text"}, pg.ColumnDef{Name: "q1", Type: "numeric"}, pg.ColumnDef{Name: "q2", Type: "numeric"},
	pg.ColumnDef{Name: "q3", Type: "numeric"}, pg.ColumnDef{Name: "q4", Type: "numeric"})
show(sel().Exprs(ref[string](ct, "region"), ref[float64](ct, "q1"), ref[float64](ct, "q2"), ref[float64](ct, "q3"), ref[float64](ct, "q4")).From(ct).Build())
Output:
SELECT "ct"."region", "ct"."q1", "ct"."q2", "ct"."q3", "ct"."q4" FROM crosstab($1, $2) AS "ct" ("region" text, "q1" numeric, "q2" numeric, "q3" numeric, "q4" numeric)
args: 2
Example (S18_02_dynamicPivot)

### 18.2 Dynamic pivot: generate the FILTER query from the data \gexec is psql. The generating SELECT renders.

s := tbl("sales", "sales")
quarter := sqlb.Col[string](s, "quarter")
q := sqlb.Derived(sel().Distinct().Exprs(quarter).From(s), "q")
qQuarter := ref[string](q, "quarter")
cols := pg.StringAgg(pg.Format("sum(amount) FILTER (WHERE quarter = %L) AS %I", qQuarter, qQuarter), ", ", pg.OrderWithin(asc(qQuarter)))
show(sel().Exprs(pg.Format("SELECT region, %s FROM sales GROUP BY region ORDER BY region", cols)).From(q).Build())
Output:
SELECT format($1, string_agg(format($2, "q"."quarter", "q"."quarter"), $3 ORDER BY "q"."quarter" ASC)) FROM (SELECT DISTINCT "sales"."quarter" FROM "sales" AS "sales") AS "q"
args: 3
Example (S18_03_unpivot)

### 18.3 Unpivot

s := tbl("sales_wide", "s")
region, id := sqlb.Col[string](s, "region"), sqlb.Col[int64](s, "id")
q := func(n string) sqlb.Expr[float64] { return sqlb.Col[float64](s, n) }
v := pg.Values("v", []string{"quarter", "amount"},
	[]sqlb.SQLer{sqlb.Param("Q1"), q("q1")}, []sqlb.SQLer{sqlb.Param("Q2"), q("q2")}, []sqlb.SQLer{sqlb.Param("Q3"), q("q3")}, []sqlb.SQLer{sqlb.Param("Q4"), q("q4")})
show(sel().Exprs(region, ref[string](v, "quarter"), ref[float64](v, "amount")).From(s).Join(crossLateral(v)).Build())
sRow := pg.RowRef(s)
kv := pg.FuncSource(sqlb.Fn[[]byte]("jsonb_each_text", pg.DeleteKey(pg.ToJSONB(sRow), "id")), "kv", "key", "value")
show(sel().Exprs(id, ref[string](kv, "key"), ref[string](kv, "value")).From(s).Join(cross(kv)).Build())
Output:
SELECT "s"."region", "v"."quarter", "v"."amount" FROM "sales_wide" AS "s" CROSS JOIN LATERAL (VALUES ($1, "s"."q1"), ($2, "s"."q2"), ($3, "s"."q3"), ($4, "s"."q4")) AS "v" ("quarter", "amount")
args: 4
SELECT "s"."id", "kv"."key", "kv"."value" FROM "sales_wide" AS "s" CROSS JOIN jsonb_each_text((to_jsonb("s") - $1)) AS "kv" ("key", "value")
args: 1
Example (S18_04_latestRowPerKey)

### 18.4 Latest row per key CREATE INDEX is DDL. The DISTINCT ON renders.

r := tbl("readings", "readings")
device, ts, reading := sqlb.Col[int64](r, "device_id"), sqlb.Col[time.Time](r, "ts"), sqlb.Col[float64](r, "reading")
show(sel().Clause(pg.DistinctOn(device)).Exprs(device, ts, reading).From(r).Order(asc(device), desc(ts)).Build())
Output:
SELECT DISTINCT ON ("readings"."device_id") "readings"."device_id", "readings"."ts", "readings"."reading" FROM "readings" AS "readings" ORDER BY "readings"."device_id" ASC, "readings"."ts" DESC
Example (S18_05_testData)

### 18.5 Realistic test data

show(sel().Exprs(pg.Setseed(0.42)).Build())
g, n := pg.GenerateSeries(1, 100000, 0, "g")
u := tbl("users", "users")
id, email, created, plan, score, tags := sqlb.Col[int64](u, "id"), sqlb.Col[string](u, "email"), sqlb.Col[time.Time](u, "created_at"), sqlb.Col[string](u, "plan"), sqlb.Col[float64](u, "score"), sqlb.Col[[]string](u, "tags")
random := pg.Random()
planPick := pg.SubscriptExpr(pg.Array("free", "pro", "team"), pg.WidthBucketThresholds(random, []float64{0, 0.7, 0.95}))
i, _ := pg.GenerateSeries(1, 4, 0, "i")
tagList := pg.ArraySubquery[string](sel().Exprs(pg.ConcatOp(sqlb.Param("tag"), sqlb.Cast[string](ref[int64](i, "n"), "text"))).From(i))
show(sqlb.NewInsert(d).Into(u).Columns(id, email, created, plan, score, tags).Select(sel().Exprs(
	n, pg.ConcatOp(pg.ConcatOp(sqlb.Param("user"), sqlb.Cast[string](n, "text")), sqlb.Param("@example.com")),
	pg.SubIntervalExpr(pg.Now(), pg.IntervalMul(pg.IntervalLit("365 days"), random)),
	planPick, pg.RandomNormal(50, 10), tagList,
).From(g)).Build())
Output:
SELECT setseed($1)
args: 1
INSERT INTO "users" AS "users" ("id", "email", "created_at", "plan", "score", "tags")
SELECT "g"."n", (($1 || CAST("g"."n" AS text)) || $2), (now() - (random() * interval '365 days')), (ARRAY[CAST($3 AS text), CAST($4 AS text), CAST($5 AS text)])[width_bucket(random(), $6)], random_normal($7, $8), ARRAY(SELECT ($9 || CAST("i"."n" AS text)) FROM generate_series(CAST($10 AS bigint), CAST($11 AS bigint)) AS "i" ("n")) FROM generate_series(CAST($12 AS bigint), CAST($13 AS bigint)) AS "g" ("n")
args: 13
Example (S18_06_textHistogram)

### 18.6 Text histogram

r := tbl("requests", "requests")
latency := sqlb.Col[float64](r, "latency_ms")
bucket := pg.WidthBucket(latency, 0, 1000, 10)
show(sel().Exprs(sqlb.As(bucket, "bucket"), sqlb.As(sqlb.Mul(sqlb.Sub(bucket, sqlb.Param[int32](1)), sqlb.Param[int32](100)), "from_ms"), sqlb.As(sqlb.Count(), "n"),
	sqlb.As(pg.RepeatExpr(sqlb.Param("#"), sqlb.Cast[int32](sqlb.Div(sqlb.Mul(sqlb.Param(50.0), sqlb.Cast[float64](sqlb.Count(), "float8")), sqlb.Cast[float64](sqlb.Over(sqlb.Max(sqlb.Count()), sqlb.NewWindow()), "float8")), "int")), "bar")).
	From(r).GroupBy(bucket).Order(asc(bucket)).Build())
Output:
SELECT width_bucket("requests"."latency_ms", $1, $2, $3) AS "bucket", ((width_bucket("requests"."latency_ms", $4, $5, $6) - $7) * $8) AS "from_ms", count(*) AS "n", repeat($9, CAST((($10 * CAST(count(*) AS float8)) / CAST(max(count(*)) OVER () AS float8)) AS int)) AS "bar" FROM "requests" AS "requests" GROUP BY width_bucket("requests"."latency_ms", $11, $12, $13) ORDER BY width_bucket("requests"."latency_ms", $14, $15, $16) ASC
args: 16
Example (S18_07_fetchWithTies)

### 18.7 FETCH FIRST ... WITH TIES (PG13)

s := tbl("scores", "scores")
score := sqlb.Col[int32](s, "score")
show(sel().Exprs(sqlb.Col[[]byte](s, "*")).From(s).Order(desc(score)).Clause(pg.FetchFirst(3, true)).Build())
Output:
SELECT "scores"."*" FROM "scores" AS "scores" ORDER BY "scores"."score" DESC FETCH FIRST $1 ROWS WITH TIES
args: 1
Example (S19_01_strictFunnel)

### 19.1 Strict-order funnel

ev := tbl("events", "events")
user := sqlb.Col[int64](ev, "user_id")
u := sqlb.Derived(sel().Distinct().Exprs(user).From(ev), "u")
uUser := ref[int64](u, "user_id")
e := tbl("events", "e")
eUser, eName, eTs := sqlb.Col[int64](e, "user_id"), sqlb.Col[string](e, "name"), sqlb.Col[time.Time](e, "ts")
step := func(alias, col, name string, after *sqlb.Expr[time.Time]) sqlb.Source {
	s := sel().Exprs(sqlb.As(sqlb.Min(eTs), col)).From(e).Where(sqlb.EqExpr(eUser, uUser), sqlb.Eq(eName, name))
	if after != nil {
		s = s.Where(sqlb.GreaterExpr(eTs, *after))
	}
	return sqlb.Derived(s, alias)
}
a := step("a", "s1", "view", nil)
aS1 := ref[time.Time](a, "s1")
b := step("b", "s2", "add_to_cart", &aS1)
bS2 := ref[time.Time](b, "s2")
c := step("c", "s3", "purchase", &bS2)
s1, s3 := ref[time.Time](a, "s1"), ref[time.Time](c, "s3")
show(sel().Exprs(sqlb.As(sqlb.Count(), "users"), sqlb.As(sqlb.CountOf(s1), "viewed"), sqlb.As(sqlb.CountOf(ref[time.Time](b, "s2")), "added"), sqlb.As(sqlb.CountOf(s3), "purchased"),
	sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), sqlb.Cast[float64](sqlb.CountOf(s3), "float8")), sqlb.AssertNotNull(sqlb.NullIf(sqlb.Cast[float64](sqlb.CountOf(s1), "float8"), sqlb.Param(0.0)))), 1), "conversion_pct")).
	From(u).Join(leftLateral(a), leftLateral(b), leftLateral(c)).Build())
Output:
SELECT count(*) AS "users", count("a"."s1") AS "viewed", count("b"."s2") AS "added", count("c"."s3") AS "purchased", round((($1 * CAST(count("c"."s3") AS float8)) / NULLIF(CAST(count("a"."s1") AS float8), $2))::numeric, $3) AS "conversion_pct" FROM (SELECT DISTINCT "events"."user_id" FROM "events" AS "events") AS "u" LEFT JOIN LATERAL (SELECT min("e"."ts") AS "s1" FROM "events" AS "e" WHERE ("e"."user_id" = "u"."user_id" AND "e"."name" = $4)) AS "a" ON (TRUE) LEFT JOIN LATERAL (SELECT min("e"."ts") AS "s2" FROM "events" AS "e" WHERE ("e"."user_id" = "u"."user_id" AND "e"."name" = $5 AND "e"."ts" > "a"."s1")) AS "b" ON (TRUE) LEFT JOIN LATERAL (SELECT min("e"."ts") AS "s3" FROM "events" AS "e" WHERE ("e"."user_id" = "u"."user_id" AND "e"."name" = $6 AND "e"."ts" > "b"."s2")) AS "c" ON (TRUE)
args: 6
Example (S19_02_cohortRetention)

### 19.2 Cohort retention matrix

o := tbl("orders", "orders")
user, created := sqlb.Col[int64](o, "user_id"), sqlb.Col[time.Time](o, "created_at")
cohorts := sel().Exprs(user, sqlb.As(sqlb.Cast[time.Time](pg.DateTrunc(pg.FieldMonth, sqlb.Min(created)), "date"), "cohort")).From(o).GroupBy(user)
activity := sel().Distinct().Exprs(user, sqlb.As(sqlb.Cast[time.Time](pg.DateTrunc(pg.FieldMonth, created), "date"), "month")).From(o)
c, a := tbl("cohorts", "c"), tbl("activity", "a")
cUser, cCohort, aUser, aMonth := sqlb.Col[int64](c, "user_id"), sqlb.Col[time.Time](c, "cohort"), sqlb.Col[int64](a, "user_id"), sqlb.Col[time.Time](a, "month")
size := sqlb.Cast[float64](sqlb.CountOf(cUser, sqlb.Distinct()), "float8")
m := func(n string) sqlb.Expr[float64] {
	retained := sqlb.CountOf(aUser, sqlb.Distinct(), pg.Filter(sqlb.EqExpr(aMonth, pg.AddIntervalExpr(cCohort, pg.IntervalLit(n)))))
	return pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), sqlb.Cast[float64](retained, "float8")), size), 1)
}
show(sel().With("cohorts", cohorts).With("activity", activity).
	Exprs(cCohort, sqlb.As(sqlb.CountOf(cUser, sqlb.Distinct()), "size"), sqlb.As(m("1 month"), "m1"), sqlb.As(m("2 months"), "m2"), sqlb.As(m("3 months"), "m3")).
	From(c).Join(sqlb.Join{Type: sqlb.JoinLeft, Table: a, Using: []string{"user_id"}}).GroupBy(cCohort).Order(asc(cCohort)).Build())
Output:
WITH "cohorts" AS (SELECT "orders"."user_id", CAST(date_trunc('month', min("orders"."created_at")) AS date) AS "cohort" FROM "orders" AS "orders" GROUP BY "orders"."user_id"),
"activity" AS (SELECT DISTINCT "orders"."user_id", CAST(date_trunc('month', "orders"."created_at") AS date) AS "month" FROM "orders" AS "orders")
SELECT "c"."cohort", count(DISTINCT "c"."user_id") AS "size", round((($1 * CAST(count(DISTINCT "a"."user_id") FILTER (WHERE "a"."month" = ("c"."cohort" + interval '1 month')) AS float8)) / CAST(count(DISTINCT "c"."user_id") AS float8))::numeric, $2) AS "m1", round((($3 * CAST(count(DISTINCT "a"."user_id") FILTER (WHERE "a"."month" = ("c"."cohort" + interval '2 months')) AS float8)) / CAST(count(DISTINCT "c"."user_id") AS float8))::numeric, $4) AS "m2", round((($5 * CAST(count(DISTINCT "a"."user_id") FILTER (WHERE "a"."month" = ("c"."cohort" + interval '3 months')) AS float8)) / CAST(count(DISTINCT "c"."user_id") AS float8))::numeric, $6) AS "m3" FROM "cohorts" AS "c" LEFT JOIN "activity" AS "a" USING ("user_id") GROUP BY "c"."cohort" ORDER BY "c"."cohort" ASC
args: 6
Example (S19_03_rollingActiveUsers)

### 19.3 Rolling 30-day active users

days, day := pg.GenerateSeriesTime(t0, t1, "1 day", "d")
e := tbl("events", "e")
eUser, eTs := sqlb.Col[int64](e, "user_id"), sqlb.Col[time.Time](e, "ts")
mau := sel().Exprs(sqlb.CountOf(eUser, sqlb.Distinct())).From(e).
	Where(sqlb.GreaterEqExpr(eTs, pg.SubIntervalExpr(day, pg.IntervalLit("29 days"))), sqlb.LessExpr(eTs, pg.AddIntervalExpr(day, pg.IntervalLit("1 day"))))
show(sel().Exprs(sqlb.Cast[time.Time](day, "date"), sqlb.As(sqlb.Subquery[int64](mau), "mau")).From(days).Build())
Output:
SELECT CAST("d"."ts" AS date), (SELECT count(DISTINCT "e"."user_id") FROM "events" AS "e" WHERE ("e"."ts" >= ("d"."ts" - interval '29 days') AND "e"."ts" < ("d"."ts" + interval '1 day'))) AS "mau" FROM generate_series($1, $2, interval '1 day') AS "d" ("ts")
args: 2
Example (S19_04_zScore)

### 19.4 Z-score anomaly detection against the trailing window

m := tbl("metrics", "metrics")
ts, value := sqlb.Col[time.Time](m, "ts"), sqlb.Col[float64](m, "value")
w := sqlb.NewWindow().OrderBy(asc(ts)).Frame(sqlb.FrameRows, sqlb.Preceding(100), sqlb.Preceding(1))
z := sqlb.Div(sqlb.Sub(value, sqlb.OverNamed(sqlb.Avg(value), "w")), sqlb.AssertNotNull(sqlb.NullIf(sqlb.OverNamed(pg.Stddev(value), "w"), sqlb.Param(0.0))))
s := sqlb.Derived(sel().Exprs(ts, value, sqlb.As(z, "z")).From(m).Window("w", w), "s")
show(sel().Exprs(ref[time.Time](s, "ts"), ref[float64](s, "value"), ref[float64](s, "z")).From(s).Where(sqlb.Greater(pg.Abs(ref[float64](s, "z")), 3)).Build())
Output:
SELECT "s"."ts", "s"."value", "s"."z" FROM (SELECT "metrics"."ts", "metrics"."value", (("metrics"."value" - avg("metrics"."value") OVER "w") / NULLIF(stddev("metrics"."value") OVER "w", $1)) AS "z" FROM "metrics" AS "metrics" WINDOW "w" AS (ORDER BY "metrics"."ts" ASC ROWS BETWEEN 100 PRECEDING AND 1 PRECEDING)) AS "s" WHERE (abs("s"."z") > $2)
args: 2
Example (S19_05_pareto)

### 19.5 Pareto: which customers make 80% of revenue

cr := tbl("customer_revenue", "customer_revenue")
customer, revenue := sqlb.Col[int64](cr, "customer_id"), sqlb.Col[float64](cr, "revenue")
running := sqlb.NewWindow().OrderBy(desc(revenue)).Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.CurrentRow())
show(sel().Exprs(customer, revenue,
	sqlb.As(sqlb.Div(sqlb.Over(sqlb.Sum(revenue), running), sqlb.Over(sqlb.Sum(revenue), sqlb.NewWindow())), "cumulative_share"),
	sqlb.As(sqlb.Div(sqlb.Cast[float64](sqlb.RowNumber().Over(sqlb.NewWindow().OrderBy(desc(revenue))), "numeric"), sqlb.Cast[float64](sqlb.Over(sqlb.Count(), sqlb.NewWindow()), "numeric")), "customer_share")).
	From(cr).Order(desc(revenue)).Build())
Output:
SELECT "customer_revenue"."customer_id", "customer_revenue"."revenue", (sum("customer_revenue"."revenue") OVER (ORDER BY "customer_revenue"."revenue" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / sum("customer_revenue"."revenue") OVER ()) AS "cumulative_share", (CAST(row_number() OVER (ORDER BY "customer_revenue"."revenue" DESC) AS numeric) / CAST(count(*) OVER () AS numeric)) AS "customer_share" FROM "customer_revenue" AS "customer_revenue" ORDER BY "customer_revenue"."revenue" DESC
Example (S19_06_allocateAcrossBuckets)

### 19.6 Allocate session time across hour buckets

s := tbl("sessions", "s")
start, end := sqlb.Col[time.Time](s, "start_ts"), sqlb.Col[time.Time](s, "end_ts")
buckets, bucket := pg.GenerateSeriesExpr(pg.DateTrunc(pg.FieldHour, start), pg.DateTrunc(pg.FieldHour, end), pg.IntervalLit("1 hour"), "bucket")
busy := pg.Diff(pg.Least(end, pg.AddIntervalExpr(bucket, pg.IntervalLit("1 hour"))), pg.Greatest(start, bucket))
show(sel().Exprs(bucket, sqlb.As(sqlb.Aggregate[pg.Interval]("sum", []sqlb.SQLer{busy}), "busy_time")).From(s).Join(crossLateral(buckets)).
	GroupBy(bucket).Order(asc(bucket)).Build())
Output:
SELECT "bucket"."ts", sum((LEAST("s"."end_ts", ("bucket"."ts" + interval '1 hour')) - GREATEST("s"."start_ts", "bucket"."ts"))) AS "busy_time" FROM "sessions" AS "s" CROSS JOIN LATERAL generate_series(date_trunc('hour', "s"."start_ts"), date_trunc('hour', "s"."end_ts"), interval '1 hour') AS "bucket" ("ts") GROUP BY "bucket"."ts" ORDER BY "bucket"."ts" ASC
Example (S19_07_gapsInSequence)

### 19.7 Missing ids / gaps in a sequence

o := tbl("orders", "orders")
id := sqlb.Col[int64](o, "id")
s := sqlb.Derived(sel().Exprs(id, sqlb.As(pg.Lead(id, 1).Over(sqlb.NewWindow().OrderBy(asc(id))), "next_id")).From(o), "s")
sID, next := ref[int64](s, "id"), sqlb.AssertNotNull(ref[*int64](s, "next_id"))
show(sel().Exprs(sqlb.As(sqlb.Add(sID, sqlb.Param[int64](1)), "gap_start"), sqlb.As(sqlb.Sub(next, sqlb.Param[int64](1)), "gap_end")).From(s).
	Where(sqlb.Greater(sqlb.Sub(next, sID), 1)).Build())
Output:
SELECT ("s"."id" + $1) AS "gap_start", ("s"."next_id" - $2) AS "gap_end" FROM (SELECT "orders"."id", lead("orders"."id", $3) OVER (ORDER BY "orders"."id" ASC) AS "next_id" FROM "orders" AS "orders") AS "s" WHERE (("s"."next_id" - "s"."id") > $4)
args: 4
Example (S19_08_weightedMedian)

### 19.8 Weighted median

samples := tbl("samples", "samples")
value, weight := sqlb.Col[float64](samples, "value"), sqlb.Col[float64](samples, "weight")
s := sqlb.Derived(sel().Exprs(value, sqlb.As(sqlb.Over(sqlb.Sum(weight), sqlb.NewWindow().OrderBy(asc(value))), "cum"), sqlb.As(sqlb.Over(sqlb.Sum(weight), sqlb.NewWindow()), "total")).From(samples), "s")
sValue, cum, total := ref[float64](s, "value"), ref[float64](s, "cum"), ref[float64](s, "total")
show(sel().Exprs(sValue).From(s).Where(sqlb.GreaterEqExpr(cum, sqlb.Div(total, sqlb.Param(2.0)))).Order(asc(sValue)).Limit(1).Build())
Output:
SELECT "s"."value" FROM (SELECT "samples"."value", sum("samples"."weight") OVER (ORDER BY "samples"."value" ASC) AS "cum", sum("samples"."weight") OVER () AS "total" FROM "samples" AS "samples") AS "s" WHERE ("s"."cum" >= ("s"."total" / $1)) ORDER BY "s"."value" ASC LIMIT $2
args: 2
Example (S19_09_p95PerMinute)

### 19.9 p95 latency per minute with request counts

r := tbl("requests", "requests")
ts, latency := sqlb.Col[time.Time](r, "ts"), sqlb.Col[float64](r, "latency_ms")
minute := pg.DateTrunc(pg.FieldMinute, ts)
show(sel().Exprs(sqlb.As(minute, "minute"), sqlb.As(sqlb.Count(), "requests"), sqlb.As(pg.PercentileCont(0.95, asc(latency)), "p95"), sqlb.As(sqlb.Max(latency), "worst")).
	From(r).Where(sqlb.GreaterExpr(ts, pg.SubIntervalExpr(pg.Now(), pg.IntervalLit("1 hour")))).GroupBy(minute).Order(asc(minute)).Build())
Output:
SELECT date_trunc('minute', "requests"."ts") AS "minute", count(*) AS "requests", percentile_cont($1) WITHIN GROUP (ORDER BY "requests"."latency_ms" ASC) AS "p95", max("requests"."latency_ms") AS "worst" FROM "requests" AS "requests" WHERE ("requests"."ts" > (now() - interval '1 hour')) GROUP BY date_trunc('minute', "requests"."ts") ORDER BY date_trunc('minute', "requests"."ts") ASC
args: 1
Example (S19_10_forwardFill)

### 19.10 Forward-fill a sparse series onto a regular grid

grid, gTs := pg.GenerateSeriesTime(t0, t1, "5 minutes", "g")
r := tbl("readings", "r")
sensor, rTs, value := sqlb.Col[int64](r, "sensor_id"), sqlb.Col[time.Time](r, "ts"), sqlb.Col[float64](r, "value")
last := sqlb.Derived(sel().Exprs(value).From(r).Where(sqlb.Eq(sensor, 3), sqlb.LessEqExpr(rTs, gTs)).Order(desc(rTs)).Limit(1), "r")
show(sel().Exprs(gTs, ref[*float64](last, "value")).From(grid).Join(leftLateral(last)).Build())
Output:
SELECT "g"."ts", "r"."value" FROM generate_series($1, $2, interval '5 minutes') AS "g" ("ts") LEFT JOIN LATERAL (SELECT "r"."value" FROM "readings" AS "r" WHERE ("r"."sensor_id" = $3 AND "r"."ts" <= "g"."ts") ORDER BY "r"."ts" DESC LIMIT $4) AS "r" ON (TRUE)
args: 4
Example (S1_01_treeWalkCycleGuard)

### 1.1 Tree walk with a manual cycle guard (any version)

emp := tbl("employees", "e")
eID, eMgr, eName := sqlb.Col[int64](emp, "id"), sqlb.Col[*int64](emp, "manager_id"), sqlb.Col[string](emp, "name")
org := tbl("org", "o")
oID, oDepth, oPath := ref[int64](org, "id"), ref[int32](org, "depth"), ref[[]int64](org, "path")

// The anchor uses the bare table (no alias), so the step's alias e is free.
anchorT := tbl("employees", "employees")
aID, aMgr, aName := sqlb.Col[int64](anchorT, "id"), sqlb.Col[*int64](anchorT, "manager_id"), sqlb.Col[string](anchorT, "name")
seed := sel().Exprs(aID, aMgr, aName, sqlb.As(sqlb.Param[int32](0), "depth"), sqlb.As(pg.ArrayOf(aID), "path")).
	From(anchorT).Where(sqlb.IsNull(aMgr))
step := sel().Exprs(eID, eMgr, eName, sqlb.Add(oDepth, sqlb.Param[int32](1)), pg.ArrayConcat(oPath, pg.ArrayOf(eID))).
	From(emp).Join(inner(org, sqlb.EqExpr(eMgr, sqlb.Nullable(oID)))).
	Where(pg.AllExpr(eID, sqlb.OpNotEq, oPath)) // cycle guard

show(sel().WithRecursive("org", seed, step).
	Exprs(sqlb.As(pg.ConcatOp(pg.RepeatExpr(sqlb.Param("    "), oDepth), ref[string](org, "name")), "tree"), oDepth, oPath).
	From(org).Order(asc(oPath)).Build())
Output:
WITH RECURSIVE "org" AS (SELECT "employees"."id", "employees"."manager_id", "employees"."name", $1 AS "depth", ARRAY["employees"."id"] AS "path" FROM "employees" AS "employees" WHERE ("employees"."manager_id" IS NULL) UNION ALL SELECT "e"."id", "e"."manager_id", "e"."name", ("o"."depth" + $2), ("o"."path" || ARRAY["e"."id"]) FROM "employees" AS "e" INNER JOIN "org" AS "o" ON ("e"."manager_id" = "o"."id") WHERE ("e"."id" <> ALL("o"."path")))
SELECT (repeat($3, "o"."depth") || "o"."name") AS "tree", "o"."depth", "o"."path" FROM "org" AS "o" ORDER BY "o"."path" ASC
args: 3
Example (S1_02_searchAndCycle)

### 1.2 Built-in SEARCH and CYCLE clauses (PG14)

emp := tbl("employees", "e")
eID, eMgr, eName := sqlb.Col[int64](emp, "id"), sqlb.Col[*int64](emp, "manager_id"), sqlb.Col[string](emp, "name")
org := tbl("org", "o")
anchorT := tbl("employees", "employees")
seed := sel().Exprs(sqlb.Col[int64](anchorT, "id"), sqlb.Col[*int64](anchorT, "manager_id"), sqlb.Col[string](anchorT, "name")).
	From(anchorT).Where(sqlb.Eq(sqlb.Col[int64](anchorT, "id"), 1))
step := sel().Exprs(eID, eMgr, eName).From(emp).Join(inner(org, sqlb.EqExpr(eMgr, sqlb.Nullable(ref[int64](org, "id")))))

show(sel().WithRecursiveCTE(sqlb.CTE{
	Name:   "org",
	Body:   seed.UnionAll(step),
	Suffix: pg.Both(pg.SearchDepthFirst("ordercol", "name"), pg.Cycle("is_cycle", "cycle_path", "id")),
}).
	Exprs(ref[string](org, "name"), ref[bool](org, "is_cycle"), ref[[]byte](org, "cycle_path")).
	From(org).Where(sqlb.Not(sqlb.PredicateOf(ref[bool](org, "is_cycle").SQL))).
	Order(asc(ref[string](org, "ordercol"))).Build())
Output:
WITH RECURSIVE "org" AS (SELECT "employees"."id", "employees"."manager_id", "employees"."name" FROM "employees" AS "employees" WHERE ("employees"."id" = $1) UNION ALL SELECT "e"."id", "e"."manager_id", "e"."name" FROM "employees" AS "e" INNER JOIN "org" AS "o" ON ("e"."manager_id" = "o"."id")) SEARCH DEPTH FIRST BY "name" SET "ordercol" CYCLE "id" SET "is_cycle" USING "cycle_path" SELECT "o"."name", "o"."is_cycle", "o"."cycle_path" FROM "org" AS "o" WHERE (NOT ("o"."is_cycle")) ORDER BY "o"."ordercol" ASC
args: 1
Example (S1_03_billOfMaterials)

### 1.3 Bill of materials with quantity multiplication

pt := tbl("parts_tree", "pt")
ptParent, ptChild, ptQty := sqlb.Col[string](pt, "parent_id"), sqlb.Col[string](pt, "child_id"), sqlb.Col[float64](pt, "qty")
bom := tbl("bom", "b")
bRoot, bChild, bTotal, bLevel, bPath := ref[string](bom, "root"), ref[string](bom, "child_id"), ref[float64](bom, "total_qty"), ref[int32](bom, "level"), ref[[]string](bom, "path")
anchor := tbl("parts_tree", "parts_tree")
aParent, aChild, aQty := sqlb.Col[string](anchor, "parent_id"), sqlb.Col[string](anchor, "child_id"), sqlb.Col[float64](anchor, "qty")

seed := sel().Exprs(sqlb.As(aParent, "root"), aChild, sqlb.As(sqlb.Cast[float64](aQty, "numeric"), "total_qty"),
	sqlb.As(sqlb.Param[int32](1), "level"), sqlb.As(pg.ArrayOf(aParent, aChild), "path")).
	From(anchor).Where(sqlb.Eq(aParent, "BIKE"))
step := sel().Exprs(bRoot, ptChild, sqlb.Mul(bTotal, ptQty), sqlb.Add(bLevel, sqlb.Param[int32](1)), pg.ArrayConcat(bPath, pg.ArrayOf(ptChild))).
	From(bom).Join(inner(pt, sqlb.EqExpr(ptParent, bChild))).
	Where(pg.AllExpr(ptChild, sqlb.OpNotEq, bPath))

show(sel().WithRecursive("bom", seed, step).
	Exprs(sqlb.As(bChild, "part"), sqlb.As(sqlb.Sum(bTotal), "needed"), sqlb.As(sqlb.Max(bLevel), "deepest_level")).
	From(bom).GroupBy(bChild).Order(desc(sqlb.OutputCol[float64]("needed"))).Build())
Output:
WITH RECURSIVE "bom" AS (SELECT "parts_tree"."parent_id" AS "root", "parts_tree"."child_id", CAST("parts_tree"."qty" AS numeric) AS "total_qty", $1 AS "level", ARRAY["parts_tree"."parent_id", "parts_tree"."child_id"] AS "path" FROM "parts_tree" AS "parts_tree" WHERE ("parts_tree"."parent_id" = $2) UNION ALL SELECT "b"."root", "pt"."child_id", ("b"."total_qty" * "pt"."qty"), ("b"."level" + $3), ("b"."path" || ARRAY["pt"."child_id"]) FROM "bom" AS "b" INNER JOIN "parts_tree" AS "pt" ON ("pt"."parent_id" = "b"."child_id") WHERE ("pt"."child_id" <> ALL("b"."path")))
SELECT "b"."child_id" AS "part", sum("b"."total_qty") AS "needed", max("b"."level") AS "deepest_level" FROM "bom" AS "b" GROUP BY "b"."child_id" ORDER BY "needed" DESC
args: 3
Example (S1_04_cheapestPath)

### 1.4 Cheapest path in a weighted graph

edges := tbl("edges", "e")
eSrc, eDst, eCost := sqlb.Col[string](edges, "src"), sqlb.Col[string](edges, "dst"), sqlb.Col[float64](edges, "cost")
paths := tbl("paths", "p")
pSrc, pDst, pCost, pPath := ref[string](paths, "src"), ref[string](paths, "dst"), ref[float64](paths, "cost"), ref[[]string](paths, "path")
anchor := tbl("edges", "edges")
aSrc, aDst, aCost := sqlb.Col[string](anchor, "src"), sqlb.Col[string](anchor, "dst"), sqlb.Col[float64](anchor, "cost")

seed := sel().Exprs(aSrc, aDst, aCost, sqlb.As(pg.ArrayOf(aSrc, aDst), "path")).From(anchor).Where(sqlb.Eq(aSrc, "A"))
step := sel().Exprs(pSrc, eDst, sqlb.Add(pCost, eCost), pg.ArrayConcat(pPath, pg.ArrayOf(eDst))).
	From(paths).Join(inner(edges, sqlb.EqExpr(eSrc, pDst))).
	Where(pg.AllExpr(eDst, sqlb.OpNotEq, pPath), sqlb.Less(pg.Cardinality(pPath), 10)) // hop cap

show(sel().WithRecursive("paths", seed, step).Clause(pg.DistinctOn(pDst)).
	Exprs(pDst, pCost, pPath).From(paths).Order(asc(pDst), asc(pCost)).Build())
Output:
WITH RECURSIVE "paths" AS (SELECT "edges"."src", "edges"."dst", "edges"."cost", ARRAY["edges"."src", "edges"."dst"] AS "path" FROM "edges" AS "edges" WHERE ("edges"."src" = $1) UNION ALL SELECT "p"."src", "e"."dst", ("p"."cost" + "e"."cost"), ("p"."path" || ARRAY["e"."dst"]) FROM "paths" AS "p" INNER JOIN "edges" AS "e" ON ("e"."src" = "p"."dst") WHERE ("e"."dst" <> ALL("p"."path") AND cardinality("p"."path") < $2))
SELECT DISTINCT ON ("p"."dst") "p"."dst", "p"."cost", "p"."path" FROM "paths" AS "p" ORDER BY "p"."dst" ASC, "p"."cost" ASC
args: 2
Example (S1_05_connectedComponents)

### 1.5 Connected components (label propagation)

nodes := tbl("nodes", "nodes")
nID := sqlb.Col[int64](nodes, "id")
edges := tbl("edges", "e")
eA, eB := sqlb.Col[int64](edges, "a"), sqlb.Col[int64](edges, "b")
reach := tbl("reach", "r")
rNode, rReached := ref[int64](reach, "node"), ref[int64](reach, "reached")

seed := sel().Exprs(sqlb.As(nID, "node"), sqlb.As(nID, "reached")).From(nodes)
other := sqlb.Case[int64]().When(sqlb.EqExpr(eA, rReached), eB).Else(eA)
step := sel().Exprs(rNode, other).From(reach).Join(inner(edges, sqlb.InExprs(rReached, eA, eB)))
// UNION, not UNION ALL, is what terminates this recursion.
show(sel().WithRecursiveCTE(sqlb.CTE{Name: "reach", Body: seed.Union(step)}).
	Exprs(rNode, sqlb.As(sqlb.Min(rReached), "component")).From(reach).GroupBy(rNode).
	Order(asc(sqlb.OutputCol[int64]("component")), asc(rNode)).Build())
Output:
WITH RECURSIVE "reach" AS (SELECT "nodes"."id" AS "node", "nodes"."id" AS "reached" FROM "nodes" AS "nodes" UNION SELECT "r"."node", CASE WHEN "e"."a" = "r"."reached" THEN "e"."b" ELSE "e"."a" END FROM "reach" AS "r" INNER JOIN "edges" AS "e" ON ("r"."reached" IN ("e"."a", "e"."b")))
SELECT "r"."node", min("r"."reached") AS "component" FROM "reach" AS "r" GROUP BY "r"."node" ORDER BY "component" ASC, "r"."node" ASC
Example (S1_06_subtreeTotals)

### 1.6 Subtree totals for every node at once

cats := tbl("categories", "c")
cID, cParent, cAmount := sqlb.Col[int64](cats, "id"), sqlb.Col[*int64](cats, "parent_id"), sqlb.Col[float64](cats, "amount")
t := tbl("t", "t")
tID, tPath := ref[int64](t, "id"), ref[[]int64](t, "path")
anchor := tbl("categories", "categories")
aID, aParent, aAmount := sqlb.Col[int64](anchor, "id"), sqlb.Col[*int64](anchor, "parent_id"), sqlb.Col[float64](anchor, "amount")

seed := sel().Exprs(aID, aParent, aAmount, sqlb.As(pg.ArrayOf(aID), "path")).From(anchor).Where(sqlb.IsNull(aParent))
step := sel().Exprs(cID, cParent, cAmount, pg.ArrayConcat(tPath, pg.ArrayOf(cID))).
	From(cats).Join(inner(t, sqlb.EqExpr(cParent, sqlb.Nullable(tID))))

a := tbl("t", "a")
dd := tbl("t", "d")
show(sel().WithRecursive("t", seed, step).
	Exprs(sqlb.Col[int64](a, "id"), sqlb.As(sqlb.Sum(sqlb.Col[float64](dd, "amount")), "subtree_total"),
		sqlb.As(sqlb.Sub(sqlb.Count(), sqlb.Param[int64](1)), "descendants")).
	From(a).Join(inner(dd, pg.AnyExpr(sqlb.Col[int64](a, "id"), sqlb.OpEq, sqlb.Col[[]int64](dd, "path")))).
	GroupBy(sqlb.Col[int64](a, "id")).Build())
Output:
WITH RECURSIVE "t" AS (SELECT "categories"."id", "categories"."parent_id", "categories"."amount", ARRAY["categories"."id"] AS "path" FROM "categories" AS "categories" WHERE ("categories"."parent_id" IS NULL) UNION ALL SELECT "c"."id", "c"."parent_id", "c"."amount", ("t"."path" || ARRAY["c"."id"]) FROM "categories" AS "c" INNER JOIN "t" AS "t" ON ("c"."parent_id" = "t"."id"))
SELECT "a"."id", sum("d"."amount") AS "subtree_total", (count(*) - $1) AS "descendants" FROM "t" AS "a" INNER JOIN "t" AS "d" ON ("a"."id" = ANY("d"."path")) GROUP BY "a"."id"
args: 1
Example (S1_07_ancestorsOfMany)

### 1.7 Ancestors of many starting nodes in one query

c := tbl("categories", "c")
cID, cParent := sqlb.Col[int64](c, "id"), sqlb.Col[*int64](c, "parent_id")
up := tbl("up", "u")
uStart, uParent, uDist := ref[int64](up, "start_id"), ref[*int64](up, "parent_id"), ref[int32](up, "dist")
p := tbl("categories", "p")
pID, pParent := sqlb.Col[int64](p, "id"), sqlb.Col[*int64](p, "parent_id")

seed := sel().Exprs(sqlb.As(cID, "start_id"), cID, cParent, sqlb.As(sqlb.Param[int32](0), "dist")).
	From(c).Where(pg.InArray(cID, []int64{3, 7}))
step := sel().Exprs(uStart, pID, pParent, sqlb.Add(uDist, sqlb.Param[int32](1))).
	From(up).Join(inner(p, sqlb.EqExpr(sqlb.Nullable(pID), uParent)))

show(sel().WithRecursive("up", seed, step).
	Exprs(uStart, sqlb.As(pg.ArrayAgg(ref[int64](up, "id"), pg.OrderWithin(desc(uDist))), "ancestors_root_first")).
	From(up).GroupBy(uStart).Build())
Output:
WITH RECURSIVE "up" AS (SELECT "c"."id" AS "start_id", "c"."id", "c"."parent_id", $1 AS "dist" FROM "categories" AS "c" WHERE ("c"."id" = ANY($2)) UNION ALL SELECT "u"."start_id", "p"."id", "p"."parent_id", ("u"."dist" + $3) FROM "up" AS "u" INNER JOIN "categories" AS "p" ON ("p"."id" = "u"."parent_id"))
SELECT "u"."start_id", array_agg("u"."id" ORDER BY "u"."dist" DESC) AS "ancestors_root_first" FROM "up" AS "u" GROUP BY "u"."start_id"
args: 3
Example (S1_08_amortization)

### 1.8 Loan amortization schedule (row depends on previous row)

params := tbl("params", "pr")
principal, r, n := ref[float64](params, "principal"), ref[float64](params, "r"), ref[int32](params, "n")
pmtT := tbl("pmt", "p")
pmt := ref[float64](pmtT, "pmt")
sched := tbl("sched", "s")
sPeriod, sBalance := ref[int32](sched, "period"), ref[float64](sched, "balance")

paramsBody := sel().Exprs(sqlb.As(sqlb.Literal[float64]("250000::numeric"), "principal"), sqlb.As(sqlb.Literal[float64]("0.05::numeric / 12"), "r"), sqlb.As(sqlb.Param[int32](360), "n"))
// round(principal * r / (1 - power(1 + r, -n)), 2)
pmtBody := sel().Exprs(sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(principal, r),
	sqlb.Sub(sqlb.Param(1.0), pg.PowerExpr(sqlb.Add(sqlb.Param(1.0), r), sqlb.Neg(n)))), 2), "pmt")).From(params)
interest := pg.Round(sqlb.Mul(sBalance, r), 2)
seed := sel().Exprs(sqlb.As(sqlb.Param[int32](0), "period"), sqlb.As(principal, "balance"),
	sqlb.As(sqlb.Literal[float64]("0::numeric"), "interest"), sqlb.As(sqlb.Literal[float64]("0::numeric"), "principal_paid")).From(params)
step := sel().Exprs(sqlb.Add(sPeriod, sqlb.Param[int32](1)),
	pg.Round(sqlb.Sub(sBalance, sqlb.Sub(pmt, interest)), 2), interest, sqlb.Sub(pmt, interest)).
	From(sched).Join(cross(pmtT), cross(params)).Where(sqlb.LessExpr(sPeriod, n))

// WITH RECURSIVE applies to the whole list, so the plain CTEs sit before it.
show(sel().With("params", paramsBody).With("pmt", pmtBody).WithRecursive("sched", seed, step).
	Exprs(sPeriod, sBalance, ref[float64](sched, "interest"), ref[float64](sched, "principal_paid")).From(sched).Build())
Output:
WITH RECURSIVE "params" AS (SELECT 250000::numeric AS "principal", 0.05::numeric / 12 AS "r", $1 AS "n"),
"pmt" AS (SELECT round((("pr"."principal" * "pr"."r") / ($2 - power(($3 + "pr"."r"), (-"pr"."n"))))::numeric, $4) AS "pmt" FROM "params" AS "pr"),
"sched" AS (SELECT $5 AS "period", "pr"."principal" AS "balance", 0::numeric AS "interest", 0::numeric AS "principal_paid" FROM "params" AS "pr" UNION ALL SELECT ("s"."period" + $6), round(("s"."balance" - ("p"."pmt" - round(("s"."balance" * "pr"."r")::numeric, $7)))::numeric, $8), round(("s"."balance" * "pr"."r")::numeric, $9), ("p"."pmt" - round(("s"."balance" * "pr"."r")::numeric, $10)) FROM "sched" AS "s" CROSS JOIN "pmt" AS "p" CROSS JOIN "params" AS "pr" WHERE ("s"."period" < "pr"."n"))
SELECT "s"."period", "s"."balance", "s"."interest", "s"."principal_paid" FROM "sched" AS "s"
args: 10
Example (S1_09_monthChunks)

### 1.9 Split a date interval into calendar-month chunks (proration)

subs := tbl("subscriptions", "subscriptions")
sID, sStart, sEnd := sqlb.Col[int64](subs, "id"), sqlb.Col[time.Time](subs, "start_date"), sqlb.Col[time.Time](subs, "end_date")
chunks := tbl("chunks", "c")
cID, cStart, cEnd, cEndDate := ref[int64](chunks, "id"), ref[time.Time](chunks, "chunk_start"), ref[time.Time](chunks, "chunk_end"), ref[time.Time](chunks, "end_date")
month := pg.IntervalLit("1 month")

seed := sel().Exprs(sID, sqlb.As(sStart, "chunk_start"),
	sqlb.As(pg.Least(sEnd, sqlb.Cast[time.Time](pg.AddIntervalExpr(pg.DateTrunc(pg.FieldMonth, sStart), month), "date")), "chunk_end"),
	sEnd).From(subs)
step := sel().Exprs(cID, cEnd, pg.Least(cEndDate, sqlb.Cast[time.Time](pg.AddIntervalExpr(cEnd, month), "date")), cEndDate).
	From(chunks).Where(sqlb.LessExpr(cEnd, cEndDate))

show(sel().WithRecursive("chunks", seed, step).
	Exprs(cID, cStart, cEnd, sqlb.As(pg.DaysBetween(cEnd, cStart), "days")).
	From(chunks).Order(asc(cID), asc(cStart)).Build())
Output:
WITH RECURSIVE "chunks" AS (SELECT "subscriptions"."id", "subscriptions"."start_date" AS "chunk_start", LEAST("subscriptions"."end_date", CAST((date_trunc('month', "subscriptions"."start_date") + interval '1 month') AS date)) AS "chunk_end", "subscriptions"."end_date" FROM "subscriptions" AS "subscriptions" UNION ALL SELECT "c"."id", "c"."chunk_end", LEAST("c"."end_date", CAST(("c"."chunk_end" + interval '1 month') AS date)),
"c"."end_date" FROM "chunks" AS "c" WHERE ("c"."chunk_end" < "c"."end_date"))
SELECT "c"."id", "c"."chunk_start", "c"."chunk_end", ("c"."chunk_end" - "c"."chunk_start") AS "days" FROM "chunks" AS "c" ORDER BY "c"."id" ASC, "c"."chunk_start" ASC
Example (S20_01_uuidV7Timestamp)

### 20.1 UUID v7 on any version, and extracting its timestamp The CREATE FUNCTION is DDL. The timestamp extraction is pg.UUIDv7Timestamp.

s := sqlb.Derived(sel().Exprs(sqlb.As(sqlb.Fn[string]("uuid_generate_v7"), "u")), "s")
show(sel().Exprs(sqlb.As(pg.UUIDv7Timestamp(ref[string](s, "u")), "created_at")).From(s).Build())
Output:
SELECT to_timestamp(('x' || substr(replace("s"."u"::text, '-', ''), 1, 12))::bit(48)::bigint / 1000.0) AS "created_at" FROM (SELECT uuid_generate_v7() AS "u") AS "s"
Example (S20_02_pgcrypto)

### 20.2 Password hashing and encryption with pgcrypto CREATE EXTENSION is DDL. The statements render through the pgcrypto helpers.

u := tbl("users", "users")
id, email, hash := sqlb.Col[int64](u, "id"), sqlb.Col[string](u, "email"), sqlb.Col[string](u, "pw_hash")
show(sqlb.NewInsert(d).Into(u).Row(sqlb.Set(email, "a@x.com"), sqlb.SetExpr(hash, crypto.Crypt(sqlb.Param("secret"), crypto.GenSalt("bf", 12)))).Build())
show(sel().Exprs(id).From(u).Where(sqlb.Eq(email, "a@x.com"), sqlb.EqExpr(hash, crypto.Crypt(sqlb.Param("secret"), hash))).Build())
show(sel().Exprs(pg.Encode(crypto.HMAC(sqlb.Param("payload"), sqlb.Param("secret"), "sha256"), "hex"), pg.Encode(crypto.Digest(sqlb.Param("x"), "sha256"), "hex")).Build())
vault := tbl("vault", "vault")
show(sel().Exprs(crypto.PgpSymEncrypt(sqlb.Param("card number"), sqlb.Param("key")), crypto.PgpSymDecrypt(sqlb.Col[[]byte](vault, "blob"), sqlb.Param("key"))).From(vault).Build())
Output:
INSERT INTO "users" AS "users" ("email", "pw_hash") VALUES ($1, crypt($2, gen_salt($3, $4)))
args: 4
SELECT "users"."id" FROM "users" AS "users" WHERE ("users"."email" = $1 AND "users"."pw_hash" = crypt($2, "users"."pw_hash"))
args: 2
SELECT encode(hmac($1, $2, $3), $4), encode(digest($5, $6), $7)
args: 7
SELECT pgp_sym_encrypt($1, $2), pgp_sym_decrypt("vault"."blob", $3) FROM "vault" AS "vault"
args: 3
Example (S20_03_base62)

### 20.3 Base62 short ids The CREATE FUNCTION is DDL. Its body is a recursive CTE, which renders.

dd := tbl("d", "d")
rem, out := ref[int64](dd, "rem"), ref[string](dd, "out")
seed := sel().Exprs(sqlb.As(sqlb.Param[int64](123456789), "rem"), sqlb.As(sqlb.Cast[string](sqlb.Param(""), "text"), "out"))
step := sel().Exprs(sqlb.Div(rem, sqlb.Param[int64](62)),
	pg.ConcatOp(pg.SubstrExpr(sqlb.Param("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"),
		sqlb.Add(sqlb.Cast[int32](sqlb.Mod(rem, sqlb.Param[int64](62)), "int"), sqlb.Param[int32](1)), sqlb.Param(1)), out)).
	From(dd).Where(sqlb.Greater(rem, 0))
final := sqlb.Subquery[string](sel().Exprs(out).From(dd).Where(sqlb.Eq(rem, 0)))
zero := "0"
show(sel().WithRecursive("d", seed, step).Exprs(sqlb.Coalesce(sqlb.NullIf(sqlb.AssertNotNull(final), sqlb.Param("")), sqlb.Param(&zero))).Build())
Output:
WITH RECURSIVE "d" AS (SELECT $1 AS "rem", CAST($2 AS text) AS "out" UNION ALL SELECT ("d"."rem" / $3), (substr($4, (CAST(("d"."rem" % $5) AS int) + $6), $7) || "d"."out") FROM "d" AS "d" WHERE ("d"."rem" > $8))
SELECT COALESCE(NULLIF((SELECT "d"."out" FROM "d" AS "d" WHERE ("d"."rem" = $9)), $10), $11)
args: 11
Example (S20_04_slugs)

### 20.4 Slugs

show(sel().Exprs(pg.TrimChars(pg.TrimBoth, "-", pg.RegexpReplace(sqlb.Lower(unaccent.Unaccent(sqlb.Param("Crème Brûlée & Co."))), "[^a-z0-9]+", "-", "g"))).Build())
Output:
SELECT trim(BOTH $1 FROM regexp_replace(lower(unaccent($2)), $3, $4, $5))
args: 5
Example (S20_05_greatCircle)

### 20.5 Great-circle distance without PostGIS

p := tbl("places", "places")
id, name, lat, lng := sqlb.Col[int64](p, "id"), sqlb.Col[string](p, "name"), sqlb.Col[float64](p, "lat"), sqlb.Col[float64](p, "lng")
sq := func(e sqlb.Expr[float64]) sqlb.Expr[float64] { return pg.PowerOp(e, sqlb.Param(2)) }
myLat, myLng := sqlb.Param(48.85), sqlb.Param(2.35)
h := sqlb.Add(sq(pg.Sin(sqlb.Div(pg.Radians(sqlb.Sub(lat, myLat)), sqlb.Param(2.0)))),
	sqlb.Mul(sqlb.Mul(pg.Cos(pg.Radians(myLat)), pg.Cos(pg.Radians(lat))), sq(pg.Sin(sqlb.Div(pg.Radians(sqlb.Sub(lng, myLng)), sqlb.Param(2.0))))))
km := sqlb.Mul(sqlb.Mul(sqlb.Param(6371.0), sqlb.Param(2.0)), pg.Asin(pg.Sqrt(h)))
show(sel().Exprs(id, name, sqlb.As(km, "km")).From(p).Order(asc(sqlb.OutputCol[float64]("km"))).Limit(10).Build())
// earthdistance: CREATE EXTENSION and CREATE INDEX are DDL; the indexed predicate renders.
show(sel().Exprs(id).From(p).Where(earth.BoxContains(earth.Box(earth.LLToEarth(myLat, myLng), 5000), earth.LLToEarth(lat, lng))).Build())
Output:
SELECT "places"."id", "places"."name", (($1 * $2) * asin(sqrt(((sin((radians(("places"."lat" - $3)) / $4)) ^ $5) + ((cos(radians($6)) * cos(radians("places"."lat"))) * (sin((radians(("places"."lng" - $7)) / $8)) ^ $9)))))) AS "km" FROM "places" AS "places" ORDER BY "km" ASC LIMIT $10
args: 10
SELECT "places"."id" FROM "places" AS "places" WHERE ((earth_box(ll_to_earth($1, $2), $3) @> ll_to_earth("places"."lat", "places"."lng")))
args: 3
Example (S20_06_ltree)

### 20.6 ltree hierarchies CREATE EXTENSION and CREATE INDEX are DDL. The four queries render.

c := tbl("categories", "categories")
path := sqlb.Col[ltree.Path](c, "path")
show(sel().Exprs(path).From(c).Where(ltree.DescendantOf(path, ltree.Lit("electronics.phones"))).Build())
show(sel().Exprs(path).From(c).Where(ltree.AncestorOf(path, ltree.Lit("electronics.phones.android"))).Build())
show(sel().Exprs(path).From(c).Where(ltree.MatchesLQuery(path, "electronics.*{1}")).Build())
show(sel().Exprs(ltree.NLevel(path), ltree.Subpath(path, 0, 2), ltree.Index(path, "phones")).From(c).Build())
Output:
SELECT "categories"."path" FROM "categories" AS "categories" WHERE (("categories"."path" <@ $1::ltree))
args: 1
SELECT "categories"."path" FROM "categories" AS "categories" WHERE (("categories"."path" @> $1::ltree))
args: 1
SELECT "categories"."path" FROM "categories" AS "categories" WHERE (("categories"."path" ~ $1::lquery))
args: 1
SELECT nlevel("categories"."path"), subpath("categories"."path", $1, $2), index("categories"."path", $3::ltree) FROM "categories" AS "categories"
args: 3
Example (S20_08_updatableView)

### 20.8 Updatable views with CHECK OPTION CREATE VIEW is DDL. The UPDATE through the view renders like any table.

v := tbl("active_users", "active_users")
show(sqlb.NewUpdate(d).Table(v).Set(sqlb.SetExpr(sqlb.Col[*time.Time](v, "deleted_at"), sqlb.Nullable(pg.Now()))).Where(sqlb.Eq(sqlb.Col[int64](v, "id"), 1)).Build())
Output:
UPDATE "active_users" AS "active_users" SET "deleted_at" = now() WHERE ("active_users"."id" = $1)
args: 1
Example (S20_09_searchEveryColumn)

### 20.9 Search every text column of a table

u := tbl("users", "u")
uRow := pg.RowRef(u)
show(sel().Exprs(sqlb.Col[[]byte](u, "*")).From(u).
	Where(pg.ILike(sqlb.Cast[string](pg.ToJSONB(uRow), "text"), "%needle%")).Build())
Output:
SELECT "u"."*" FROM "users" AS "u" WHERE ((CAST(to_jsonb("u") AS text) ILIKE $1))
args: 1
Example (S20_10_generateInserts)

### 20.10 Generate INSERT statements for a subset of rows

u := tbl("users", "u")
uRow := pg.RowRef(u)
show(sel().Exprs(pg.Format("INSERT INTO %I SELECT * FROM jsonb_populate_record(NULL::%I, %L);", sqlb.Param("users"), sqlb.Param("users"), pg.ToJSONB(uRow))).
	From(u).Where(sqlb.Less(sqlb.Col[int64](u, "id"), 10)).Build())
Output:
SELECT format($1, $2, $3, to_jsonb("u")) FROM "users" AS "u" WHERE ("u"."id" < $4)
args: 4
Example (S20_11_validateInput)

### 20.11 Validate input without exceptions (PG16)

show(sel().Exprs(pg.InputIsValid("abc", "integer"), pg.InputIsValid("2024-02-30", "date"), pg.InputIsValid(`{"a":`, "jsonb")).Build())
info := pg.FuncSource(sqlb.Fn[[]byte]("pg_input_error_info", sqlb.Param("abc"), sqlb.Param("integer")), "e")
show(sel().Exprs(sqlb.Col[[]byte](sqlb.Ref(info), "*")).From(info).Build())
Output:
SELECT pg_input_is_valid($1, $2), pg_input_is_valid($3, $4), pg_input_is_valid($5, $6)
args: 6
SELECT "e"."*" FROM pg_input_error_info($1, $2) AS "e"
args: 2
Example (S20_12_systemColumns)

### 20.12 MVCC system columns and transaction ids

t := tbl("t", "t")
show(sel().Exprs(sqlb.Col[string](t, "ctid"), sqlb.Col[int64](t, "xmin"), sqlb.Col[int64](t, "xmax"), sqlb.Col[[]byte](t, "*")).From(t).Where(sqlb.Eq(sqlb.Col[int64](t, "id"), 1)).Build())
show(sel().Exprs(pg.CurrentXactID(), pg.CurrentXactIDIfAssigned(), pg.TxidCurrent()).Build())
show(sel().Exprs(pg.ExportSnapshot()).Build())
Output:
SELECT "t"."ctid", "t"."xmin", "t"."xmax", "t"."*" FROM "t" AS "t" WHERE ("t"."id" = $1)
args: 1
SELECT pg_current_xact_id(), pg_current_xact_id_if_assigned(), txid_current()
SELECT pg_export_snapshot()
Example (S20_14_foreignTable)

### 20.14 Foreign tables and cross-database queries CREATE EXTENSION, CREATE SERVER, USER MAPPING and IMPORT FOREIGN SCHEMA are DDL. The query over the foreign table renders with a schema-qualified Table.

ev := sqlb.Table{Schema: "remote", Name: "events", Alias: "e"}
created := sqlb.Col[time.Time](ev, "created_at")
show(sel().Exprs(sqlb.Col[[]byte](ev, "*")).From(ev).Where(sqlb.GreaterExpr(created, pg.SubIntervalExpr(pg.Now(), pg.IntervalLit("1 day")))).Build())
Output:
SELECT "e"."*" FROM "remote"."events" AS "e" WHERE ("e"."created_at" > (now() - interval '1 day'))
Example (S20_15_bitFlags)

### 20.15 Bit flags

u := tbl("users", "users")
perms, id := sqlb.Col[int32](u, "perms"), sqlb.Col[int64](u, "id")
show(sel().Exprs(id).From(u).Where(sqlb.NotEq(pg.BitAnd(perms, sqlb.Param[int32](4)), 0)).Build())
show(sqlb.NewUpdate(d).Table(u).Set(sqlb.SetExpr(perms, pg.BitOr(perms, sqlb.Param[int32](8)))).Where(sqlb.Eq(id, 1)).Build())
show(sqlb.NewUpdate(d).Table(u).Set(sqlb.SetExpr(perms, pg.BitAnd(perms, pg.BitNot(sqlb.Param[int32](8))))).Where(sqlb.Eq(id, 1)).Build())
show(sel().Exprs(pg.BitStringOr(pg.BitLit("1011"), pg.BitLit("0100")), pg.GetBit(pg.BitLit("1011"), 0), pg.BitCount(pg.BitLit("1011"))).Build())
Output:
SELECT "users"."id" FROM "users" AS "users" WHERE (("users"."perms" & $1) <> $2)
args: 2
UPDATE "users" AS "users" SET "perms" = ("users"."perms" | $1) WHERE ("users"."id" = $2)
args: 2
UPDATE "users" AS "users" SET "perms" = ("users"."perms" & (~$1)) WHERE ("users"."id" = $2)
args: 2
SELECT (B'1011' | B'0100'), get_bit(B'1011', $1), bit_count(B'1011')
args: 1
Example (S20_16_logicalDecoding)

### 20.16 Logical decoding peek (change data capture without a client)

show(sel().Exprs(sqlb.Fn[[]byte]("pg_create_logical_replication_slot", sqlb.Param("debug_slot"), sqlb.Param("test_decoding"))).Build())
changes := pg.FuncSource(sqlb.Fn[[]byte]("pg_logical_slot_get_changes", sqlb.Param("debug_slot"), sqlb.Literal[*string]("NULL"), sqlb.Literal[*string]("NULL")), "c")
show(sel().Exprs(sqlb.Col[[]byte](sqlb.Ref(changes), "*")).From(changes).Build())
show(sel().Exprs(sqlb.Fn[[]byte]("pg_drop_replication_slot", sqlb.Param("debug_slot"))).Build())
Output:
SELECT pg_create_logical_replication_slot($1, $2)
args: 2
SELECT "c"."*" FROM pg_logical_slot_get_changes($1, NULL, NULL) AS "c"
args: 1
SELECT pg_drop_replication_slot($1)
args: 1
Example (S2_01_gapsAndIslands)

### 2.1 Gaps and islands: consecutive-day streaks

act := tbl("activity", "activity")
aUser, aAt := sqlb.Col[int64](act, "user_id"), sqlb.Col[time.Time](act, "activity_at")
days := sqlb.Derived(sel().Distinct().Exprs(aUser, sqlb.As(sqlb.Cast[time.Time](aAt, "date"), "day")).From(act), "d")
dUser, dDay := ref[int64](days, "user_id"), ref[time.Time](days, "day")
rn := sqlb.RowNumber().Over(sqlb.NewWindow().PartitionBy(dUser).OrderBy(asc(dDay)))
inner := sel().Exprs(dUser, dDay, sqlb.As(pg.DateMinusDays(dDay, sqlb.Cast[int32](rn, "int")), "grp")).From(days)
s := sqlb.Derived(inner, "s")
sUser, sDay, sGrp := ref[int64](s, "user_id"), ref[time.Time](s, "day"), ref[time.Time](s, "grp")

show(sel().Exprs(sUser, sqlb.As(sqlb.Min(sDay), "streak_start"), sqlb.As(sqlb.Max(sDay), "streak_end"), sqlb.As(sqlb.Count(), "days")).
	From(s).GroupBy(sUser, sGrp).Order(asc(sUser), asc(sqlb.OutputCol[time.Time]("streak_start"))).Build())
Output:
SELECT "s"."user_id", min("s"."day") AS "streak_start", max("s"."day") AS "streak_end", count(*) AS "days" FROM (SELECT "d"."user_id", "d"."day", ("d"."day" - CAST(row_number() OVER (PARTITION BY "d"."user_id" ORDER BY "d"."day" ASC) AS int)) AS "grp" FROM (SELECT DISTINCT "activity"."user_id", CAST("activity"."activity_at" AS date) AS "day" FROM "activity" AS "activity") AS "d") AS "s" GROUP BY "s"."user_id", "s"."grp" ORDER BY "s"."user_id" ASC, "streak_start" ASC
Example (S2_02_sessionization)

### 2.2 Sessionization with an inactivity gap

ev := tbl("events", "events")
eUser, eTs := sqlb.Col[int64](ev, "user_id"), sqlb.Col[time.Time](ev, "ts")
byUser := sqlb.NewWindow().PartitionBy(eUser).OrderBy(asc(eTs))
ordered := sel().Exprs(eUser, eTs, sqlb.As(pg.Lag(eTs, 1).Over(byUser), "prev_ts")).From(ev)

o := tbl("ordered", "o")
oUser, oTs, oPrev := ref[int64](o, "user_id"), ref[time.Time](o, "ts"), ref[*time.Time](o, "prev_ts")
gap := sqlb.GreaterExpr(pg.Diff(oTs, sqlb.AssertNotNull(oPrev)), pg.IntervalLit("30 minutes"))
flagged := sel().Exprs(oUser, oTs, oPrev,
	sqlb.As(sqlb.Case[int32]().When(sqlb.Or(sqlb.IsNull(oPrev), gap), sqlb.Param[int32](1)).Else(sqlb.Param[int32](0)), "new_session")).From(o)

f := tbl("flagged", "f")
fUser, fTs, fNew := ref[int64](f, "user_id"), ref[time.Time](f, "ts"), ref[int32](f, "new_session")
running := sqlb.NewWindow().PartitionBy(fUser).OrderBy(asc(fTs)).Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.CurrentRow())
numbered := sel().Exprs(fUser, fTs, sqlb.As(sqlb.Over(sqlb.Sum(fNew), running), "session_no")).From(f)

n := tbl("numbered", "n")
nUser, nTs, nSess := ref[int64](n, "user_id"), ref[time.Time](n, "ts"), ref[int64](n, "session_no")
show(sel().With("ordered", ordered).With("flagged", flagged).With("numbered", numbered).
	Exprs(nUser, nSess, sqlb.As(sqlb.Min(nTs), "started"), sqlb.As(sqlb.Max(nTs), "ended"), sqlb.As(sqlb.Count(), "events"),
		sqlb.As(pg.Diff(sqlb.Max(nTs), sqlb.Min(nTs)), "duration")).
	From(n).GroupBy(nUser, nSess).Order(asc(nUser), asc(nSess)).Build())
Output:
WITH "ordered" AS (SELECT "events"."user_id", "events"."ts", lag("events"."ts", $1) OVER (PARTITION BY "events"."user_id" ORDER BY "events"."ts" ASC) AS "prev_ts" FROM "events" AS "events"),
"flagged" AS (SELECT "o"."user_id", "o"."ts", "o"."prev_ts", CASE WHEN ("o"."prev_ts" IS NULL OR ("o"."ts" - "o"."prev_ts") > interval '30 minutes') THEN $2 ELSE $3 END AS "new_session" FROM "ordered" AS "o"),
"numbered" AS (SELECT "f"."user_id", "f"."ts", sum("f"."new_session") OVER (PARTITION BY "f"."user_id" ORDER BY "f"."ts" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "session_no" FROM "flagged" AS "f")
SELECT "n"."user_id", "n"."session_no", min("n"."ts") AS "started", max("n"."ts") AS "ended", count(*) AS "events", (max("n"."ts") - min("n"."ts")) AS "duration" FROM "numbered" AS "n" GROUP BY "n"."user_id", "n"."session_no" ORDER BY "n"."user_id" ASC, "n"."session_no" ASC
args: 3
Example (S2_03_frames)

### 2.3 Frame clauses: RANGE with an offset, GROUPS, EXCLUDE

p := tbl("payments", "payments")
ts, amount := sqlb.Col[time.Time](p, "ts"), sqlb.Col[float64](p, "amount")
show(sel().Exprs(ts, amount,
	sqlb.As(sqlb.Over(sqlb.Sum(amount), sqlb.NewWindow().OrderBy(asc(ts)).Frame(sqlb.FrameRange, sqlb.PrecedingExpr("interval '7 days'"), sqlb.CurrentRow())), "rolling_7d"),
	sqlb.As(sqlb.Over(sqlb.Avg(amount), sqlb.NewWindow().OrderBy(asc(ts)).Frame(sqlb.FrameRows, sqlb.Preceding(3), sqlb.Following(3)).Exclude(pg.ExcludeCurrentRow)), "neighbours_avg"),
	sqlb.As(sqlb.Over(sqlb.Sum(amount), sqlb.NewWindow().OrderBy(asc(sqlb.Cast[time.Time](ts, "date"))).Frame(pg.FrameGroups, sqlb.Preceding(1), sqlb.Following(1))), "three_day_total"),
	sqlb.As(sqlb.Over(sqlb.Count(), sqlb.NewWindow().OrderBy(asc(amount)).Frame(sqlb.FrameRange, sqlb.Preceding(10), sqlb.Following(10)).Exclude(pg.ExcludeTies)), "within_10_no_ties"),
).From(p).Build())
Output:
SELECT "payments"."ts", "payments"."amount", sum("payments"."amount") OVER (ORDER BY "payments"."ts" ASC RANGE BETWEEN interval '7 days' PRECEDING AND CURRENT ROW) AS "rolling_7d", avg("payments"."amount") OVER (ORDER BY "payments"."ts" ASC ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING EXCLUDE CURRENT ROW) AS "neighbours_avg", sum("payments"."amount") OVER (ORDER BY CAST("payments"."ts" AS date) ASC GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS "three_day_total", count(*) OVER (ORDER BY "payments"."amount" ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING EXCLUDE TIES) AS "within_10_no_ties" FROM "payments" AS "payments"
Example (S2_04_defaultFrameTrap)

### 2.4 The default frame trap

p := tbl("payments", "payments")
day, amount := sqlb.Col[time.Time](p, "day"), sqlb.Col[float64](p, "amount")
byDay := sqlb.NewWindow().OrderBy(asc(day))
show(sel().Exprs(day, amount,
	sqlb.As(sqlb.Over(sqlb.Sum(amount), byDay), "peers_included"),
	sqlb.As(sqlb.Over(sqlb.Sum(amount), byDay.Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.CurrentRow())), "true_running"),
	sqlb.As(sqlb.LastValue(amount).Over(byDay), "wrong_last"),
	sqlb.As(sqlb.LastValue(amount).Over(byDay.Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.UnboundedFollowing())), "real_last"),
).From(p).Build())
Output:
SELECT "payments"."day", "payments"."amount", sum("payments"."amount") OVER (ORDER BY "payments"."day" ASC) AS "peers_included", sum("payments"."amount") OVER (ORDER BY "payments"."day" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "true_running", last_value("payments"."amount") OVER (ORDER BY "payments"."day" ASC) AS "wrong_last", last_value("payments"."amount") OVER (ORDER BY "payments"."day" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "real_last" FROM "payments" AS "payments"
Example (S2_05_carryForward)

### 2.5 Carry forward the last non-null value (IGNORE NULLS emulation)

r := tbl("readings", "readings")
ts, val := sqlb.Col[time.Time](r, "ts"), sqlb.Col[*float64](r, "val")
inner := sel().Exprs(ts, val, sqlb.As(sqlb.Over(sqlb.CountOf(val), sqlb.NewWindow().OrderBy(asc(ts))), "grp")).From(r)
s := sqlb.Derived(inner, "s")
sTs, sVal, sGrp := ref[time.Time](s, "ts"), ref[*float64](s, "val"), ref[int64](s, "grp")
show(sel().Exprs(sTs, sVal, sqlb.As(sqlb.FirstValue(sVal).Over(sqlb.NewWindow().PartitionBy(sGrp).OrderBy(asc(sTs))), "filled")).
	From(s).Order(asc(sTs)).Build())
Output:
SELECT "s"."ts", "s"."val", first_value("s"."val") OVER (PARTITION BY "s"."grp" ORDER BY "s"."ts" ASC) AS "filled" FROM (SELECT "readings"."ts", "readings"."val", count("readings"."val") OVER (ORDER BY "readings"."ts" ASC) AS "grp" FROM "readings" AS "readings") AS "s" ORDER BY "s"."ts" ASC
Example (S2_06_namedWindows)

### 2.6 Rank, share, quartile, and top-N per group with named windows

e := tbl("employees", "employees")
dept, name, salary := sqlb.Col[string](e, "dept"), sqlb.Col[string](e, "name"), sqlb.Col[float64](e, "salary")
byDept := sqlb.NewWindow().PartitionBy(dept)
inner := sel().Exprs(dept, name, salary,
	sqlb.As(sqlb.Rank().OverNamed("w"), "rnk"),
	sqlb.As(sqlb.Div(salary, sqlb.Over(sqlb.Sum(salary), byDept)), "share_of_dept"),
	sqlb.As(sqlb.Sub(salary, sqlb.Over(sqlb.Avg(salary), byDept)), "vs_dept_avg"),
	sqlb.As(pg.Ntile(4).OverNamed("w"), "quartile"),
	sqlb.As(pg.CumeDist().OverNamed("w"), "cume"),
	sqlb.As(pg.PercentRank().OverNamed("w"), "pct_rank"),
).From(e).Window("w", sqlb.NewWindow().PartitionBy(dept).OrderBy(desc(salary)))
s := sqlb.Derived(inner, "s")
show(sel().Exprs(ref[string](s, "dept"), ref[string](s, "name"), ref[float64](s, "salary"), ref[int64](s, "rnk")).
	From(s).Where(sqlb.LessEq(ref[int64](s, "rnk"), 3)).Build())
Output:
SELECT "s"."dept", "s"."name", "s"."salary", "s"."rnk" FROM (SELECT "employees"."dept", "employees"."name", "employees"."salary", rank() OVER "w" AS "rnk", ("employees"."salary" / sum("employees"."salary") OVER (PARTITION BY "employees"."dept")) AS "share_of_dept", ("employees"."salary" - avg("employees"."salary") OVER (PARTITION BY "employees"."dept")) AS "vs_dept_avg", ntile($1) OVER "w" AS "quartile", cume_dist() OVER "w" AS "cume", percent_rank() OVER "w" AS "pct_rank" FROM "employees" AS "employees" WINDOW "w" AS (PARTITION BY "employees"."dept" ORDER BY "employees"."salary" DESC)) AS "s" WHERE ("s"."rnk" <= $2)
args: 2
Example (S2_07_cumulativeDistinct)

### 2.7 Cumulative distinct count

ev := tbl("events", "events")
user, ts := sqlb.Col[int64](ev, "user_id"), sqlb.Col[time.Time](ev, "ts")
f := sqlb.Derived(sel().Exprs(user, sqlb.As(sqlb.Min(sqlb.Cast[time.Time](ts, "date")), "day")).From(ev).GroupBy(user), "f")
fDay := ref[time.Time](f, "day")
show(sel().Exprs(fDay, sqlb.As(sqlb.Over(sqlb.Sum(sqlb.Count()), sqlb.NewWindow().OrderBy(asc(fDay))), "cumulative_distinct_users")).
	From(f).GroupBy(fDay).Order(asc(fDay)).Build())
Output:
SELECT "f"."day", sum(count(*)) OVER (ORDER BY "f"."day" ASC) AS "cumulative_distinct_users" FROM (SELECT "events"."user_id", min(CAST("events"."ts" AS date)) AS "day" FROM "events" AS "events" GROUP BY "events"."user_id") AS "f" GROUP BY "f"."day" ORDER BY "f"."day" ASC
Example (S2_08_cappedRunningTotal)

### 2.8 Capped running total (window functions cannot do this)

ledger := tbl("ledger", "ledger")
lID, lAmount := sqlb.Col[int64](ledger, "id"), sqlb.Col[float64](ledger, "amount")
ordered := sel().Exprs(lID, lAmount, sqlb.As(sqlb.RowNumber().Over(sqlb.NewWindow().OrderBy(asc(lID))), "rn")).From(ledger)
o := tbl("ordered", "o")
oID, oAmount, oRn := ref[int64](o, "id"), ref[float64](o, "amount"), ref[int64](o, "rn")
run := tbl("run", "r")
rRn, rTotal := ref[int64](run, "rn"), ref[float64](run, "capped_total")
clamp := func(e sqlb.Expr[float64]) sqlb.Expr[float64] {
	return pg.Least(pg.Greatest(e, sqlb.Param(0.0)), sqlb.Param(100.0))
}
seed := sel().Exprs(oID, oAmount, oRn, sqlb.As(sqlb.Cast[float64](clamp(oAmount), "numeric"), "capped_total")).From(o).Where(sqlb.Eq(oRn, 1))
step := sel().Exprs(oID, oAmount, oRn, clamp(sqlb.Add(rTotal, oAmount))).
	From(run).Join(inner(o, sqlb.EqExpr(oRn, sqlb.Add(rRn, sqlb.Param[int64](1)))))
show(sel().With("ordered", ordered).WithRecursive("run", seed, step).
	Exprs(ref[int64](run, "id"), ref[float64](run, "amount"), rTotal).From(run).Order(asc(rRn)).Build())
Output:
WITH RECURSIVE "ordered" AS (SELECT "ledger"."id", "ledger"."amount", row_number() OVER (ORDER BY "ledger"."id" ASC) AS "rn" FROM "ledger" AS "ledger"),
"run" AS (SELECT "o"."id", "o"."amount", "o"."rn", CAST(LEAST(GREATEST("o"."amount", $1), $2) AS numeric) AS "capped_total" FROM "ordered" AS "o" WHERE ("o"."rn" = $3) UNION ALL SELECT "o"."id", "o"."amount", "o"."rn", LEAST(GREATEST(("r"."capped_total" + "o"."amount"), $4), $5) FROM "run" AS "r" INNER JOIN "ordered" AS "o" ON ("o"."rn" = ("r"."rn" + $6)))
SELECT "r"."id", "r"."amount", "r"."capped_total" FROM "run" AS "r" ORDER BY "r"."rn" ASC
args: 6
Example (S2_09_movingMedian)

### 2.9 Moving median over a time window

p := tbl("payments", "p")
pTs, pAmount := sqlb.Col[time.Time](p, "ts"), sqlb.Col[float64](p, "amount")
q := tbl("payments", "q")
qTs, qAmount := sqlb.Col[time.Time](q, "ts"), sqlb.Col[float64](q, "amount")
median := sel().Exprs(sqlb.As(pg.PercentileCont(0.5, asc(qAmount)), "median_7d")).From(q).
	Where(sqlb.GreaterExpr(qTs, pg.SubIntervalExpr(pTs, pg.IntervalLit("7 days"))), sqlb.LessEqExpr(qTs, pTs))
m := sqlb.Derived(median, "m")
show(sel().Exprs(pTs, pAmount, ref[float64](m, "median_7d")).From(p).Join(crossLateral(m)).Build())
Output:
SELECT "p"."ts", "p"."amount", "m"."median_7d" FROM "payments" AS "p" CROSS JOIN LATERAL (SELECT percentile_cont($1) WITHIN GROUP (ORDER BY "q"."amount" ASC) AS "median_7d" FROM "payments" AS "q" WHERE ("q"."ts" > ("p"."ts" - interval '7 days') AND "q"."ts" <= "p"."ts")) AS "m"
args: 1
Example (S2_10_yearOverYear)

### 2.10 Year-over-year with a division guard

mr := tbl("monthly_revenue", "monthly_revenue")
month, revenue := sqlb.Col[time.Time](mr, "month"), sqlb.Col[float64](mr, "revenue")
ly := sqlb.AssertNotNull(pg.Lag(revenue, 12).Over(sqlb.NewWindow().OrderBy(asc(month)))) // NULL propagates through the arithmetic
show(sel().Exprs(month, revenue, sqlb.As(ly, "revenue_ly"),
	sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), sqlb.Sub(revenue, ly)), sqlb.AssertNotNull(sqlb.NullIf(ly, sqlb.Param(0.0)))), 1), "yoy_pct")).
	From(mr).Build())
Output:
SELECT "monthly_revenue"."month", "monthly_revenue"."revenue", lag("monthly_revenue"."revenue", $1) OVER (ORDER BY "monthly_revenue"."month" ASC) AS "revenue_ly", round((($2 * ("monthly_revenue"."revenue" - lag("monthly_revenue"."revenue", $3) OVER (ORDER BY "monthly_revenue"."month" ASC))) / NULLIF(lag("monthly_revenue"."revenue", $4) OVER (ORDER BY "monthly_revenue"."month" ASC), $5))::numeric, $6) AS "yoy_pct" FROM "monthly_revenue" AS "monthly_revenue"
args: 6
Example (S2_11_windowOverAggregate)

### 2.11 Window over an aggregate

s := tbl("sales", "sales")
region, amount := sqlb.Col[string](s, "region"), sqlb.Col[float64](s, "amount")
total := sqlb.Sum(amount)
show(sel().Exprs(region, sqlb.As(total, "total"),
	sqlb.As(pg.Round(sqlb.Div(sqlb.Mul(sqlb.Param(100.0), total), sqlb.Over(sqlb.Sum(total), sqlb.NewWindow())), 2), "pct_of_all"),
	sqlb.As(sqlb.Rank().Over(sqlb.NewWindow().OrderBy(desc(total))), "rnk")).
	From(s).GroupBy(region).Build())
Output:
SELECT "sales"."region", sum("sales"."amount") AS "total", round((($1 * sum("sales"."amount")) / sum(sum("sales"."amount")) OVER ())::numeric, $2) AS "pct_of_all", rank() OVER (ORDER BY sum("sales"."amount") DESC) AS "rnk" FROM "sales" AS "sales" GROUP BY "sales"."region"
args: 2
Example (S3_01_topNKeepingEmptyGroups)

### 3.1 Top 3 per group, keeping groups with no rows

c := tbl("customers", "c")
cID, cName := sqlb.Col[int64](c, "id"), sqlb.Col[string](c, "name")
o := tbl("orders", "o")
oID, oCustomer, oTotal, oCreated := sqlb.Col[int64](o, "id"), sqlb.Col[int64](o, "customer_id"), sqlb.Col[float64](o, "total"), sqlb.Col[time.Time](o, "created_at")
last3 := sqlb.Derived(sel().Exprs(oID, oTotal, oCreated).From(o).Where(sqlb.EqExpr(oCustomer, cID)).Order(desc(oCreated)).Limit(3), "o")
show(sel().Exprs(cID, cName, sqlb.As(ref[int64](last3, "id"), "order_id"), ref[float64](last3, "total"), ref[time.Time](last3, "created_at")).
	From(c).Join(leftLateral(last3)).Order(asc(cID), desc(ref[time.Time](last3, "created_at"))).Build())
Output:
SELECT "c"."id", "c"."name", "o"."id" AS "order_id", "o"."total", "o"."created_at" FROM "customers" AS "c" LEFT JOIN LATERAL (SELECT "o"."id", "o"."total", "o"."created_at" FROM "orders" AS "o" WHERE ("o"."customer_id" = "c"."id") ORDER BY "o"."created_at" DESC LIMIT $1) AS "o" ON (TRUE) ORDER BY "c"."id" ASC, "o"."created_at" DESC
args: 1
Example (S3_02_zipWithOrdinality)

### 3.2 Zip arrays and set-returning functions with WITH ORDINALITY

t := pg.UnnestMulti("t", []sqlb.SQLer{pg.Array("a", "b", "c"), pg.Array(1, 2)}, true, "letter", "num", "ord")
show(sel().Exprs(ref[string](t, "letter"), ref[*int32](t, "num"), ref[int64](t, "ord")).From(t).Build())
rows := pg.RowsFrom("t", []sqlb.SQLer{
	sqlb.Fn[[]byte]("jsonb_array_elements", sqlb.Cast[[]byte](sqlb.Param([]byte(`[10,20]`)), "jsonb")),
	sqlb.Fn[int32]("generate_series", sqlb.Param(1), sqlb.Param(3)),
}, true, "j", "n", "ord")
show(sel().Exprs(ref[[]byte](rows, "j"), ref[*int32](rows, "n"), ref[int64](rows, "ord")).From(rows).Build())
Output:
SELECT "t"."letter", "t"."num", "t"."ord" FROM unnest(ARRAY[CAST($1 AS text), CAST($2 AS text), CAST($3 AS text)], ARRAY[CAST($4 AS bigint), CAST($5 AS bigint)]) WITH ORDINALITY AS "t" ("letter", "num", "ord")
args: 5
SELECT "t"."j", "t"."n", "t"."ord" FROM ROWS FROM (jsonb_array_elements(CAST($1 AS jsonb)), generate_series($2, $3)) WITH ORDINALITY AS "t" ("j", "n", "ord")
args: 3
Example (S3_03_fillMissingDays)

### 3.3 Fill missing days per entity with zeros

s := tbl("stores", "s")
sStore := sqlb.Col[int64](s, "store_id")
days, day := pg.GenerateSeriesTime(t0, t1, "1 day", "d")
o := tbl("orders", "o")
oStore, oCreated, oTotal := sqlb.Col[int64](o, "store_id"), sqlb.Col[time.Time](o, "created_at"), sqlb.Col[float64](o, "total")
show(sel().Exprs(sStore, sqlb.As(sqlb.Cast[time.Time](day, "date"), "day"), sqlb.As(sqlb.Coalesce(sqlb.Sum(oTotal), sqlb.Param(0.0)), "revenue")).
	From(s).Join(cross(days), left(o, sqlb.EqExpr(oStore, sStore), sqlb.GreaterEqExpr(oCreated, day),
	sqlb.LessExpr(oCreated, pg.AddIntervalExpr(day, pg.IntervalLit("1 day"))))).
	GroupBy(sStore, day).Order(asc(sStore), asc(day)).Build())
Output:
SELECT "s"."store_id", CAST("d"."ts" AS date) AS "day", COALESCE(sum("o"."total"), $1) AS "revenue" FROM "stores" AS "s" CROSS JOIN generate_series($2, $3, interval '1 day') AS "d" ("ts") LEFT JOIN "orders" AS "o" ON ("o"."store_id" = "s"."store_id" AND "o"."created_at" >= "d"."ts" AND "o"."created_at" < ("d"."ts" + interval '1 day')) GROUP BY "s"."store_id", "d"."ts" ORDER BY "s"."store_id" ASC, "d"."ts" ASC
args: 3
Example (S3_04_lateralAsLet)

### 3.4 LATERAL as a LET binding

p := tbl("products", "p")
pID, price, qty := sqlb.Col[int64](p, "id"), sqlb.Col[float64](p, "price"), sqlb.Col[float64](p, "qty")
calc := sqlb.Derived(sel().Exprs(sqlb.As(sqlb.Mul(price, qty), "subtotal")), "calc")
subtotal := ref[float64](calc, "subtotal")
show(sel().Exprs(pID, subtotal, sqlb.As(sqlb.Mul(subtotal, sqlb.Param(0.2)), "tax"), sqlb.As(sqlb.Mul(subtotal, sqlb.Param(1.2)), "total")).
	From(p).Join(crossLateral(calc)).Where(sqlb.Greater(subtotal, 100)).Build())
Output:
SELECT "p"."id", "calc"."subtotal", ("calc"."subtotal" * $1) AS "tax", ("calc"."subtotal" * $2) AS "total" FROM "products" AS "p" CROSS JOIN LATERAL (SELECT ("p"."price" * "p"."qty") AS "subtotal") AS "calc" WHERE ("calc"."subtotal" > $3)
args: 3
Example (S3_05_multipleSRFsInSelect)

### 3.5 Multiple set-returning functions in the SELECT list

show(sel().Exprs(sqlb.As(pg.GenerateSeriesScalar(sqlb.Param(1), sqlb.Param(3)), "a"), sqlb.As(pg.GenerateSeriesScalar(sqlb.Param(1), sqlb.Param(2)), "b")).Build())
Output:
SELECT generate_series(CAST($1 AS bigint), CAST($2 AS bigint)) AS "a", generate_series(CAST($3 AS bigint), CAST($4 AS bigint)) AS "b"
args: 4
Example (S3_06_existsWithDetail)

### 3.6 Exists-with-detail

u := tbl("users", "u")
uID, uEmail := sqlb.Col[int64](u, "id"), sqlb.Col[string](u, "email")
a := tbl("login_attempts", "a")
aUser, aAt, aOK := sqlb.Col[int64](a, "user_id"), sqlb.Col[time.Time](a, "attempted_at"), sqlb.Col[bool](a, "success")
f := sqlb.Derived(sel().Exprs(aAt).From(a).Where(sqlb.EqExpr(aUser, uID), sqlb.Not(sqlb.PredicateOf(aOK.SQL))).Order(desc(aAt)).Limit(1), "f")
fAt := ref[time.Time](f, "attempted_at")
show(sel().Exprs(uID, uEmail, sqlb.As(fAt, "last_failed_login")).From(u).Join(leftLateral(f)).
	Where(sqlb.GreaterExpr(fAt, pg.SubIntervalExpr(pg.Now(), pg.IntervalLit("1 hour")))).Build())
Output:
SELECT "u"."id", "u"."email", "f"."attempted_at" AS "last_failed_login" FROM "users" AS "u" LEFT JOIN LATERAL (SELECT "a"."attempted_at" FROM "login_attempts" AS "a" WHERE ("a"."user_id" = "u"."id" AND NOT ("a"."success")) ORDER BY "a"."attempted_at" DESC LIMIT $1) AS "f" ON (TRUE) WHERE ("f"."attempted_at" > (now() - interval '1 hour'))
args: 1
Example (S4_01_groupingSets)

### 4.1 GROUPING SETS with labelled subtotals

s := tbl("sales", "sales")
region, product, amount := sqlb.Col[string](s, "region"), sqlb.Col[string](s, "product"), sqlb.Col[float64](s, "amount")
label := func(c sqlb.Expr[string], all string) sqlb.Expr[string] {
	return sqlb.Case[string]().When(sqlb.Eq(pg.Grouping(c), 1), sqlb.Param(all)).Else(c)
}
show(sel().Exprs(sqlb.As(label(region, "ALL REGIONS"), "region"), sqlb.As(label(product, "ALL PRODUCTS"), "product"),
	sqlb.As(sqlb.Sum(amount), "total"), sqlb.As(pg.Grouping(region, product), "grouping_id")).
	From(s).GroupBy(pg.GroupingSets([]sqlb.SQLer{region, product}, []sqlb.SQLer{region}, []sqlb.SQLer{product}, nil)).
	Order(asc(pg.Grouping(region)), asc(pg.Grouping(product)), asc(region), asc(product)).Build())
Output:
SELECT CASE WHEN grouping("sales"."region") = $1 THEN $2 ELSE "sales"."region" END AS "region", CASE WHEN grouping("sales"."product") = $3 THEN $4 ELSE "sales"."product" END AS "product", sum("sales"."amount") AS "total", grouping("sales"."region", "sales"."product") AS "grouping_id" FROM "sales" AS "sales" GROUP BY GROUPING SETS (("sales"."region", "sales"."product"), ("sales"."region"), ("sales"."product"), ()) ORDER BY grouping("sales"."region") ASC, grouping("sales"."product") ASC, "sales"."region" ASC, "sales"."product" ASC
args: 4
Example (S4_02_pivotWithFilter)

### 4.2 Pivot with FILTER

s := tbl("sales", "sales")
region, quarter, amount := sqlb.Col[string](s, "region"), sqlb.Col[string](s, "quarter"), sqlb.Col[float64](s, "amount")
q := func(n string) sqlb.Expr[float64] { return sqlb.Sum(amount, pg.Filter(sqlb.Eq(quarter, n))) }
show(sel().Exprs(region, sqlb.As(q("Q1"), "q1"), sqlb.As(q("Q2"), "q2"), sqlb.As(q("Q3"), "q3"), sqlb.As(q("Q4"), "q4"),
	sqlb.As(sqlb.Count(pg.Filter(sqlb.Greater(amount, 1000))), "big_deals")).From(s).GroupBy(region).Build())
Output:
SELECT "sales"."region", sum("sales"."amount") FILTER (WHERE "sales"."quarter" = $1) AS "q1", sum("sales"."amount") FILTER (WHERE "sales"."quarter" = $2) AS "q2", sum("sales"."amount") FILTER (WHERE "sales"."quarter" = $3) AS "q3", sum("sales"."amount") FILTER (WHERE "sales"."quarter" = $4) AS "q4", count(*) FILTER (WHERE "sales"."amount" > $5) AS "big_deals" FROM "sales" AS "sales" GROUP BY "sales"."region"
args: 5
Example (S4_03_orderedAndHypotheticalSets)

### 4.3 Ordered-set and hypothetical-set aggregates

r := tbl("requests", "requests")
dept, latency, status := sqlb.Col[string](r, "dept"), sqlb.Col[float64](r, "latency_ms"), sqlb.Col[string](r, "status")
show(sel().Exprs(dept,
	sqlb.As(pg.WithinGroup[[]float64]("percentile_cont", []sqlb.SQLer{pg.Array(0.5, 0.9, 0.99)}, asc(latency)), "p50_p90_p99"),
	sqlb.As(pg.PercentileDisc(0.5, latency, sqlb.SortAsc), "median_real_value"),
	sqlb.As(pg.Mode(status), "most_common_status"),
	sqlb.As(pg.WithinGroup[int64]("rank", []sqlb.SQLer{sqlb.Param(2500.0)}, asc(latency)), "rank_of_2500ms"),
).From(r).GroupBy(dept).Build())
Output:
SELECT "requests"."dept", percentile_cont(ARRAY[CAST($1 AS float8), CAST($2 AS float8), CAST($3 AS float8)]) WITHIN GROUP (ORDER BY "requests"."latency_ms" ASC) AS "p50_p90_p99", percentile_disc($4) WITHIN GROUP (ORDER BY "requests"."latency_ms" ASC) AS "median_real_value", mode() WITHIN GROUP (ORDER BY "requests"."status" ASC) AS "most_common_status", rank($5) WITHIN GROUP (ORDER BY "requests"."latency_ms" ASC) AS "rank_of_2500ms" FROM "requests" AS "requests" GROUP BY "requests"."dept"
args: 5
Example (S4_04_aggregateModifiers)

### 4.4 string_agg / array_agg / jsonb_object_agg with DISTINCT, ORDER BY, FILTER

ol := tbl("order_lines", "order_lines")
customer, category, product, variant := sqlb.Col[int64](ol, "customer_id"), sqlb.Col[string](ol, "category"), sqlb.Col[int64](ol, "product_id"), sqlb.Col[int64](ol, "variant_id")
created, refunded, note, qty, status := sqlb.Col[time.Time](ol, "created_at"), sqlb.Col[bool](ol, "refunded"), sqlb.Col[*string](ol, "note"), sqlb.Col[int32](ol, "qty"), sqlb.Col[string](ol, "status")
show(sel().Exprs(customer,
	sqlb.As(pg.StringAgg(category, ", ", sqlb.Distinct(), pg.OrderWithin(asc(category))), "categories"),
	sqlb.As(pg.ArrayAgg(product, pg.OrderWithin(desc(created)), pg.Filter(sqlb.Not(sqlb.PredicateOf(refunded.SQL)))), "kept_products"),
	sqlb.As(sqlb.Coalesce(pg.ArrayAgg(note, pg.Filter(sqlb.IsNotNull(note))), sqlb.Literal[[]*string]("'{}'")), "notes"),
	sqlb.As(pg.JSONBObjectAgg(product, qty), "qty_by_product"),
	sqlb.As(pg.BoolAnd(sqlb.BoolExpr(sqlb.Eq(status, "paid"))), "fully_paid"),
	sqlb.As(sqlb.Aggregate[int64]("count", []sqlb.SQLer{sqlb.Row(product, variant)}, sqlb.Distinct()), "distinct_pairs"),
).From(ol).GroupBy(customer).
	Having(sqlb.EqExpr(sqlb.Count(pg.Filter(sqlb.Eq(status, "paid"))), sqlb.Count())).Build())
Output:
SELECT "order_lines"."customer_id", string_agg(DISTINCT "order_lines"."category", $1 ORDER BY "order_lines"."category" ASC) AS "categories", array_agg("order_lines"."product_id" ORDER BY "order_lines"."created_at" DESC) FILTER (WHERE NOT ("order_lines"."refunded")) AS "kept_products", COALESCE(array_agg("order_lines"."note") FILTER (WHERE "order_lines"."note" IS NOT NULL), '{}') AS "notes", jsonb_object_agg("order_lines"."product_id", "order_lines"."qty") AS "qty_by_product", bool_and("order_lines"."status" = $2) AS "fully_paid", count(DISTINCT ("order_lines"."product_id", "order_lines"."variant_id")) AS "distinct_pairs" FROM "order_lines" AS "order_lines" GROUP BY "order_lines"."customer_id" HAVING (count(*) FILTER (WHERE "order_lines"."status" = $3) = count(*))
args: 3
Example (S4_05_customAggregates)

### 4.5 Custom aggregates: first(), product() CREATE FUNCTION and CREATE AGGREGATE are DDL, not the query layer. The call that uses them renders through sqlb.Aggregate, the typed door.

r := tbl("returns", "returns")
name, created, rate := sqlb.Col[string](r, "name"), sqlb.Col[time.Time](r, "created_at"), sqlb.Col[float64](r, "rate")
show(sel().Exprs(sqlb.Aggregate[string]("first", []sqlb.SQLer{name}, pg.OrderWithin(asc(created))),
	sqlb.As(sqlb.Sub(sqlb.Aggregate[float64]("product", []sqlb.SQLer{sqlb.Add(sqlb.Param(1.0), rate)}), sqlb.Param(1.0)), "compound_growth")).
	From(r).Build())
Output:
SELECT first("returns"."name" ORDER BY "returns"."created_at" ASC), (product(($1 + "returns"."rate")) - $2) AS "compound_growth" FROM "returns" AS "returns"
args: 2
Example (S4_06_statisticalAggregates)

### 4.6 Statistical aggregates

p := tbl("products", "products")
price, sales := sqlb.Col[float64](p, "price"), sqlb.Col[float64](p, "sales")
agg2 := func(name string) sqlb.Expr[float64] { return sqlb.Aggregate[float64](name, []sqlb.SQLer{sales, price}) }
show(sel().Exprs(sqlb.As(sqlb.Aggregate[float64]("corr", []sqlb.SQLer{price, sales}), "correlation"),
	sqlb.As(agg2("regr_slope"), "slope"), sqlb.As(agg2("regr_intercept"), "intercept"), sqlb.As(agg2("regr_r2"), "r_squared"),
	sqlb.Aggregate[float64]("stddev_samp", []sqlb.SQLer{price}), sqlb.Aggregate[float64]("var_pop", []sqlb.SQLer{price}),
	sqlb.Aggregate[float64]("covar_samp", []sqlb.SQLer{price, sales})).From(p).Build())
Output:
SELECT corr("products"."price", "products"."sales") AS "correlation", regr_slope("products"."sales", "products"."price") AS "slope", regr_intercept("products"."sales", "products"."price") AS "intercept", regr_r2("products"."sales", "products"."price") AS "r_squared", stddev_samp("products"."price"), var_pop("products"."price"), covar_samp("products"."price", "products"."sales") FROM "products" AS "products"
Example (S4_07_rangeAgg)

### 4.7 range_agg and range_intersect_agg (PG14)

b := tbl("bookings", "bookings")
room, during := sqlb.Col[int64](b, "room_id"), sqlb.Col[pg.Range[time.Time]](b, "during")
show(sel().Exprs(room, sqlb.As(pg.RangeAgg(during), "busy"), sqlb.As(sqlb.Aggregate[pg.Range[time.Time]]("range_intersect_agg", []sqlb.SQLer{during}), "always_busy")).
	From(b).GroupBy(room).Build())
Output:
SELECT "bookings"."room_id", range_agg("bookings"."during") AS "busy", range_intersect_agg("bookings"."during") AS "always_busy" FROM "bookings" AS "bookings" GROUP BY "bookings"."room_id"
Example (S4_08_anyValue)

### 4.8 any_value (PG16) and the "column must appear in GROUP BY" error

o := tbl("orders", "o")
oID, oTotal, oCustomer, oCity := sqlb.Col[int64](o, "id"), sqlb.Col[float64](o, "total"), sqlb.Col[int64](o, "customer_id"), sqlb.Col[string](o, "shipping_city")
l := tbl("order_lines", "l")
show(sel().Exprs(oID, oTotal, sqlb.CountOf(sqlb.Col[[]byte](l, "*"))).From(o).Join(inner(l, sqlb.EqExpr(sqlb.Col[int64](l, "order_id"), oID))).GroupBy(oID).Build())
show(sel().Exprs(oCustomer, sqlb.As(sqlb.Aggregate[string]("any_value", []sqlb.SQLer{oCity}), "a_city")).From(o).GroupBy(oCustomer).Build())
Output:
SELECT "o"."id", "o"."total", count("l"."*") FROM "orders" AS "o" INNER JOIN "order_lines" AS "l" ON ("l"."order_id" = "o"."id") GROUP BY "o"."id"
SELECT "o"."customer_id", any_value("o"."shipping_city") AS "a_city" FROM "orders" AS "o" GROUP BY "o"."customer_id"
Example (S4_09_caseInsideAggregate)

### 4.9 The CASE-inside-aggregate trap

dp := tbl("departments", "departments")
employees, expenses := sqlb.Col[int32](dp, "employees"), sqlb.Col[float64](dp, "expenses")
perHead := sqlb.Div(expenses, sqlb.Cast[float64](employees, "float8"))
show(sel().Exprs(sqlb.Case[float64]().When(sqlb.Greater(sqlb.Min(employees), 0), sqlb.Avg(perHead)).End()).From(dp).Build())
show(sel().Exprs(sqlb.Avg(sqlb.AssertNotNull(sqlb.Case[float64]().When(sqlb.Greater(employees, 0), perHead).End()))).From(dp).Build())
Output:
SELECT CASE WHEN min("departments"."employees") > $1 THEN avg(("departments"."expenses" / CAST("departments"."employees" AS float8))) END FROM "departments" AS "departments"
args: 1
SELECT avg(CASE WHEN "departments"."employees" > $1 THEN ("departments"."expenses" / CAST("departments"."employees" AS float8)) END) FROM "departments" AS "departments"
args: 1
Example (S5_01_upsertConditional)

### 5.1 Upsert with a conditional update and inserted/updated detection

inv := tbl("inventory", "i")
sku, qty, updated := sqlb.Col[string](inv, "sku"), sqlb.Col[int64](inv, "qty"), sqlb.Col[time.Time](inv, "updated_at")
xmax := sqlb.Col[int64](inv, "xmax")
newQty := sqlb.Add(qty, pg.Excluded(qty))
show(sqlb.InsertReturning(sqlb.NewInsert(d).Into(inv).Row(sqlb.Set(sku, "A1"), sqlb.Set(qty, 5), sqlb.SetExpr(updated, pg.Now())).
	Clause(pg.OnConflict(sku).DoUpdate(sqlb.SetExpr(qty, newQty), sqlb.SetExpr(updated, pg.Excluded(updated))).
		UpdateWhere(sqlb.GreaterEq(newQty, 0)).Clause()), sqlb.Bind(sku, &sink.s), sqlb.Bind(qty, &sink.i64), sqlb.Bind(sqlb.As(sqlb.BoolExpr(sqlb.Eq(xmax, 0)), "inserted"), &sink.b)).Build())
Output:
INSERT INTO "inventory" AS "i" ("sku", "qty", "updated_at") VALUES ($1, $2, now()) ON CONFLICT ("sku") DO UPDATE SET "qty" = ("i"."qty" + EXCLUDED."qty"),
"updated_at" = EXCLUDED."updated_at" WHERE (("i"."qty" + EXCLUDED."qty") >= $3) RETURNING "i"."sku", "i"."qty", "i"."xmax" = $4 AS "inserted"
args: 4
Example (S5_02_partialIndexArbiter)

### 5.2 Partial unique index as the conflict arbiter CREATE UNIQUE INDEX is DDL. The INSERT repeats the index predicate as the arbiter.

u := tbl("users", "u")
email, name, deleted := sqlb.Col[string](u, "email"), sqlb.Col[string](u, "name"), sqlb.Col[*time.Time](u, "deleted_at")
show(sqlb.NewInsert(d).Into(u).Row(sqlb.Set(email, "a@x.com"), sqlb.Set(name, "Ada")).
	Clause(pg.OnConflict(email).TargetWhere(sqlb.IsNull(deleted)).DoUpdate(sqlb.SetExpr(name, pg.Excluded(name))).Clause()).Build())
Output:
INSERT INTO "users" AS "u" ("email", "name") VALUES ($1, $2) ON CONFLICT ("email") WHERE ("u"."deleted_at" IS NULL) DO UPDATE SET "name" = EXCLUDED."name"
args: 2
Example (S5_03_getOrCreate)

### 5.3 Get-or-create returning the existing row, and its race

tags := tbl("tags", "tags")
tID, tName := sqlb.Col[int64](tags, "id"), sqlb.Col[string](tags, "name")
ins := sqlb.InsertReturning(sqlb.NewInsert(d).Into(tags).Row(sqlb.Set(tName, "go")).Clause(pg.OnConflict(tName).DoNothing()), sqlb.Bind(tID, &sink.i64))
insT := tbl("ins", "ins")
show(sel().With("ins", ins).Exprs(ref[int64](insT, "id")).From(insT).
	UnionAll(sel().Exprs(tID).From(tags).Where(sqlb.Eq(tName, "go"))).Limit(1).Build())
Output:
WITH "ins" AS (INSERT INTO "tags" AS "tags" ("name") VALUES ($1) ON CONFLICT ("name") DO NOTHING RETURNING "tags"."id")
SELECT "ins"."id" FROM "ins" AS "ins" UNION ALL SELECT "tags"."id" FROM "tags" AS "tags" WHERE ("tags"."name" = $2) LIMIT $3
args: 3
Example (S5_04_bulkUpsertFromJSON)

### 5.4 Bulk upsert from a JSON parameter with source dedup

p := tbl("products", "products")
sku, price := sqlb.Col[string](p, "sku"), sqlb.Col[float64](p, "price")
x := pg.RecordSet(sqlb.Fn[[]byte]("jsonb_to_recordset", sqlb.Cast[[]byte](sqlb.Param([]byte(`[{"sku":"A1","price":9}]`)), "jsonb")), "x",
	pg.ColumnDef{Name: "sku", Type: "text"}, pg.ColumnDef{Name: "price", Type: "numeric"})
xSku, xPrice := ref[string](x, "sku"), ref[float64](x, "price")
src := sel().Clause(pg.DistinctOn(xSku)).Exprs(xSku, xPrice).From(x).Order(asc(xSku), desc(xPrice))
show(sqlb.NewInsert(d).Into(p).Columns(sku, price).Select(src).
	Clause(pg.OnConflict(sku).DoUpdate(sqlb.SetExpr(price, pg.Excluded(price))).
		UpdateWhere(pg.IsDistinctFrom(price, pg.Excluded(price))).Clause()).Build())
Output:
INSERT INTO "products" AS "products" ("sku", "price")
SELECT DISTINCT ON ("x"."sku") "x"."sku", "x"."price" FROM jsonb_to_recordset(CAST($1 AS jsonb)) AS "x" ("sku" text, "price" numeric) ORDER BY "x"."sku" ASC, "x"."price" DESC ON CONFLICT ("sku") DO UPDATE SET "price" = EXCLUDED."price" WHERE (("products"."price" IS DISTINCT FROM EXCLUDED."price"))
args: 1
Example (S5_05_merge)

### 5.5 MERGE with delete, conditional insert, and RETURNING (PG15, RETURNING PG17)

stock := tbl("stock", "s")
sSku, sQty, sUpdated := sqlb.Col[string](stock, "sku"), sqlb.Col[int64](stock, "qty"), sqlb.Col[time.Time](stock, "updated_at")
mv := tbl("movements", "movements")
mvSku, mvDelta, mvBatch := sqlb.Col[string](mv, "sku"), sqlb.Col[int64](mv, "delta"), sqlb.Col[int64](mv, "batch_id")
m := sqlb.Derived(sel().Exprs(mvSku, sqlb.As(sqlb.Sum(mvDelta), "delta")).From(mv).Where(sqlb.Eq(mvBatch, 42)).GroupBy(mvSku), "m")
mSku, mDelta := ref[string](m, "sku"), ref[int64](m, "delta")
show(pg.NewMerge(d).Into(stock).Using(m, sqlb.EqExpr(sSku, mSku)).
	WhenMatched(sqlb.LessEq(sqlb.Add(sQty, mDelta), 0)).ThenDelete().
	WhenMatched().ThenUpdate(sqlb.SetExpr(sQty, sqlb.Add(sQty, mDelta)), sqlb.SetExpr(sUpdated, pg.Now())).
	WhenNotMatched(sqlb.Greater(mDelta, 0)).ThenInsert(sqlb.SetExpr(sSku, mSku), sqlb.SetExpr(sQty, mDelta)).
	WhenNotMatched().ThenDoNothing().
	Returning(sqlb.Bind(pg.MergeAction(), &sink.s), sqlb.Bind(sSku, &sink.s), sqlb.Bind(sQty, &sink.i64)).Build())
Output:
MERGE INTO "stock" AS "s" USING (SELECT "movements"."sku", sum("movements"."delta") AS "delta" FROM "movements" AS "movements" WHERE ("movements"."batch_id" = $1) GROUP BY "movements"."sku") AS "m" ON ("s"."sku" = "m"."sku") WHEN MATCHED AND (("s"."qty" + "m"."delta") <= $2) THEN DELETE WHEN MATCHED THEN UPDATE SET "qty" = ("s"."qty" + "m"."delta"),
"updated_at" = now() WHEN NOT MATCHED AND ("m"."delta" > $3) THEN INSERT ("sku", "qty") VALUES ("m"."sku", "m"."delta") WHEN NOT MATCHED THEN DO NOTHING RETURNING merge_action(),
"s"."sku", "s"."qty"
args: 3
Example (S5_06_moveRows)

### 5.6 Move rows between tables atomically

jobs := tbl("jobs", "j")
status, finished := sqlb.Col[string](jobs, "status"), sqlb.Col[time.Time](jobs, "finished_at")
moved := sqlb.DeleteReturning(sqlb.NewDelete(d).From(jobs).Where(sqlb.Eq(status, "done"), sqlb.LessExpr(finished, pg.SubIntervalExpr(pg.Now(), pg.IntervalLit("7 days")))), sqlb.Bind(sqlb.Col[[]byte](jobs, "*"), &sink.j))
movedT := tbl("moved", "moved")
show(sqlb.NewInsert(d).With("moved", moved).Into(tbl("jobs_archive", "jobs_archive")).
	Select(sel().Exprs(raw("*")).From(movedT)).Build())
Output:
WITH "moved" AS (DELETE FROM "jobs" AS "j" WHERE ("j"."status" = $1 AND "j"."finished_at" < (now() - interval '7 days')) RETURNING "j"."*") INSERT INTO "jobs_archive" AS "jobs_archive" SELECT * FROM "moved" AS "moved"
args: 1
Example (S5_07_snapshotRule)

### 5.7 Data-modifying CTE snapshot rule

t := tbl("t", "t")
v := sqlb.Col[int32](t, "v")
ins := sqlb.InsertReturning(sqlb.NewInsert(d).Into(t).Row(sqlb.Set(v, 1)), sqlb.Bind(sqlb.Col[[]byte](t, "*"), &sink.j))
checkIt := sel().Exprs(sqlb.As(sqlb.Count(), "n")).From(t)
insT, checkT := tbl("ins", "ins"), tbl("check_it", "check_it")
show(sel().With("ins", ins).With("check_it", checkIt).
	Exprs(sqlb.Subquery[int64](sel().Exprs(ref[int64](checkT, "n")).From(checkT)), sqlb.Subquery[int64](sel().Exprs(sqlb.Count()).From(insT))).Build())
Output:
WITH "ins" AS (INSERT INTO "t" AS "t" ("v") VALUES ($1) RETURNING "t"."*"),
"check_it" AS (SELECT count(*) AS "n" FROM "t" AS "t")
SELECT (SELECT "check_it"."n" FROM "check_it" AS "check_it"), (SELECT count(*) FROM "ins" AS "ins")
args: 1
Example (S5_08_updateFromAggregated)

### 5.8 UPDATE ... FROM and its silent multiple-match hazard

a := tbl("accounts", "a")
aID, aBalance := sqlb.Col[int64](a, "id"), sqlb.Col[float64](a, "balance")
pending := tbl("pending", "pending")
pAccount, pDelta := sqlb.Col[int64](pending, "account_id"), sqlb.Col[float64](pending, "delta")
s := sqlb.Derived(sel().Exprs(pAccount, sqlb.As(sqlb.Sum(pDelta), "delta")).From(pending).GroupBy(pAccount), "s")
show(sqlb.NewUpdate(d).Table(a).Set(sqlb.SetExpr(aBalance, sqlb.Add(aBalance, ref[float64](s, "delta")))).
	Clause(pg.UpdateFrom(s)).Where(sqlb.EqExpr(aID, ref[int64](s, "account_id"))).Build())
Output:
UPDATE "accounts" AS "a" SET "balance" = ("a"."balance" + "s"."delta") FROM (SELECT "pending"."account_id", sum("pending"."delta") AS "delta" FROM "pending" AS "pending" GROUP BY "pending"."account_id") AS "s" WHERE ("a"."id" = "s"."account_id")
Example (S5_09_lockInOrder)

### 5.9 Lock rows in a fixed order before a multi-row update (deadlock avoidance)

a := tbl("accounts", "a")
aID, aBalance := sqlb.Col[int64](a, "id"), sqlb.Col[float64](a, "balance")
locked := sel().Exprs(aID).From(a).Where(pg.InArray(aID, []int64{1, 2, 3})).Order(asc(aID)).Clause(pg.ForUpdate())
lockedT := tbl("locked", "locked")
show(sqlb.NewUpdate(d).With("locked", locked).Table(a).Set(sqlb.SetExpr(aBalance, sqlb.Sub(aBalance, sqlb.Param(10.0)))).
	Clause(pg.UpdateFrom(lockedT)).Where(sqlb.EqExpr(aID, ref[int64](lockedT, "id"))).Build())
Output:
WITH "locked" AS (SELECT "a"."id" FROM "accounts" AS "a" WHERE ("a"."id" = ANY($1)) ORDER BY "a"."id" ASC FOR UPDATE) UPDATE "accounts" AS "a" SET "balance" = ("a"."balance" - $2) FROM "locked" AS "locked" WHERE ("a"."id" = "locked"."id")
args: 2
Example (S5_10_deleteDuplicates)

### 5.10 Delete duplicates keeping one

c := tbl("contacts", "c")
cID, cEmail := sqlb.Col[int64](c, "id"), sqlb.Col[string](c, "email")
dd := tbl("contacts", "d")
show(sqlb.NewDelete(d).From(c).Clause(pg.DeleteUsing(dd)).
	Where(sqlb.EqExpr(cEmail, sqlb.Col[string](dd, "email")), sqlb.GreaterExpr(cID, sqlb.Col[int64](dd, "id"))).Build())
// By physical row id when there is no usable id.
contacts := tbl("contacts", "contacts")
ctid, email, created := sqlb.Col[string](contacts, "ctid"), sqlb.Col[string](contacts, "email"), sqlb.Col[time.Time](contacts, "created_at")
numbered := sqlb.Derived(sel().Exprs(ctid, sqlb.As(sqlb.RowNumber().Over(sqlb.NewWindow().PartitionBy(email).OrderBy(asc(created), asc(ctid))), "rn")).From(contacts), "s")
dups := sel().Exprs(ref[string](numbered, "ctid")).From(numbered).Where(sqlb.Greater(ref[int64](numbered, "rn"), 1))
show(sqlb.NewDelete(d).From(contacts).Where(sqlb.InSubquery(ctid, dups)).Build())
Output:
DELETE FROM "contacts" AS "c" USING "contacts" AS "d" WHERE ("c"."email" = "d"."email" AND "c"."id" > "d"."id")
DELETE FROM "contacts" AS "contacts" WHERE ("contacts"."ctid" IN (SELECT "s"."ctid" FROM (SELECT "contacts"."ctid", row_number() OVER (PARTITION BY "contacts"."email" ORDER BY "contacts"."created_at" ASC, "contacts"."ctid" ASC) AS "rn" FROM "contacts" AS "contacts") AS "s" WHERE ("s"."rn" > $1)))
args: 1
Example (S5_12_mapGeneratedIds)

### 5.12 Map generated ids back to input rows

names := pg.FuncSource(sqlb.Fn[string]("unnest", sqlb.Cast[[]string](sqlb.Param([]string{"a", "b"}), "text[]")), "u", "name", "ord")
input := sel().Exprs(ref[string](names, "name"), ref[int64](names, "ord")).From(pg.WithOrdinality(names))
inputT := tbl("input", "i")
items := tbl("items", "items")
itID, itName := sqlb.Col[int64](items, "id"), sqlb.Col[string](items, "name")
ins := sqlb.InsertReturning(sqlb.NewInsert(d).Into(items).Columns(itName).
	Select(sel().Exprs(ref[string](inputT, "name")).From(inputT).Order(asc(ref[int64](inputT, "ord")))), sqlb.Bind(itID, &sink.i64), sqlb.Bind(itName, &sink.s))
insT := tbl("ins", "ins")
show(sel().With("input", input).With("ins", ins).
	Exprs(ref[int64](inputT, "ord"), ref[int64](insT, "id")).From(inputT).
	Join(inner(insT, sqlb.EqExpr(ref[string](insT, "name"), ref[string](inputT, "name")))).
	Order(asc(ref[int64](inputT, "ord"))).Build())
Output:
WITH "input" AS (SELECT "u"."name", "u"."ord" FROM unnest(CAST($1 AS text[])) WITH ORDINALITY AS "u" ("name", "ord")),
"ins" AS (INSERT INTO "items" AS "items" ("name")
SELECT "i"."name" FROM "input" AS "i" ORDER BY "i"."ord" ASC RETURNING "items"."id", "items"."name")
SELECT "i"."ord", "ins"."id" FROM "input" AS "i" INNER JOIN "ins" AS "ins" ON ("ins"."name" = "i"."name") ORDER BY "i"."ord" ASC
args: 1
Example (S5_13_overridingSystemValue)

### 5.13 Identity columns: OVERRIDING SYSTEM VALUE and resetting after a bulk load

t := tbl("t", "t")
id, v := sqlb.Col[int64](t, "id"), sqlb.Col[string](t, "v")
show(sqlb.NewInsert(d).Into(t).Row(sqlb.Set(id, 42), sqlb.Set(v, "x")).Clause(pg.OverridingSystemValue()).Build())
// setval(pg_get_serial_sequence(...)) is a maintenance call; it renders as a FROM-less SELECT.
show(sel().Exprs(pg.Setval(pg.GetSerialSequence("t", "id"), sqlb.Subquery[int64](sel().Exprs(sqlb.Max(id)).From(t)))).Build())
// ALTER TABLE ... RESTART WITH is DDL.
Example (S6_01_jsonpath)

### 6.1 JSONPath queries, predicates, and variables

o := tbl("orders", "orders")
oID, doc := sqlb.Col[int64](o, "id"), sqlb.Col[json.RawMessage](o, "doc")
items, item := pg.PathQueryVars(doc, `$.items[*] ? (@.price > $min && @.sku like_regex "^A" flag "i")`, json.RawMessage(`{"min": 10}`), "item")
show(sel().Exprs(oID, sqlb.As(item, "item")).From(o).Join(cross(items)).
	Where(pg.PathExists(doc, `$.items[*] ? (@.qty > 100)`)).Build())
show(sel().Exprs(
	sqlb.As(pg.PathQueryArray(doc, `$.items[*].price`), "prices"),
	sqlb.As(pg.PathQueryFirst(doc, `$.items[*] ? (@.sku == "A1")`), "first_a1"),
	sqlb.As(sqlb.BoolExpr(pg.PathExists(doc, `$.customer.vip ? (@ == true)`)), "is_vip"),
	sqlb.As(pg.PathQueryScalar(doc, `$.items.size()`), "n_items"),
).From(o).Build())
Output:
SELECT "orders"."id", "item"."value" AS "item" FROM "orders" AS "orders" CROSS JOIN jsonb_path_query("orders"."doc", $1::jsonpath, $2::jsonb) AS "item" ("value") WHERE ("orders"."doc" @? $3::jsonpath)
args: 3
SELECT jsonb_path_query_array("orders"."doc", $1::jsonpath) AS "prices", jsonb_path_query_first("orders"."doc", $2::jsonpath) AS "first_a1", "orders"."doc" @? $3::jsonpath AS "is_vip", jsonb_path_query("orders"."doc", $4::jsonpath) AS "n_items" FROM "orders" AS "orders"
args: 4
Example (S6_02_deepUpdates)

### 6.2 Deep updates: jsonb_set, subscripting, delete-by-path, shallow merge

o := tbl("orders", "o")
doc := sqlb.Col[json.RawMessage](o, "doc")
upd := func(as sqlb.Assignment) { show(sqlb.NewUpdate(d).Table(o).Set(as).All().Build()) }
upd(sqlb.SetExpr(doc, pg.JSONBSet(doc, []string{"customer", "address", "zip"}, json.RawMessage(`"10001"`), true)))
upd(sqlb.SetExpr(doc, pg.JSONBSetLax(doc, []string{"note"}, sqlb.Literal[json.RawMessage]("NULL"), true, "delete_key")))
upd(sqlb.SetExpr(doc, pg.JSONConcat(doc, sqlb.Param(json.RawMessage(`{"status": "shipped", "shipped_at": "2024-09-16"}`)))))
upd(sqlb.SetExpr(doc, pg.DeletePath(doc, []string{"customer", "ssn"})))
upd(pg.SetSubscript(doc, []string{"customer", "tier"}, sqlb.Param(json.RawMessage(`"gold"`))))
show(sel().Exprs(pg.JSONSubscript(doc, "customer", "address", "zip")).From(o).Build())
Output:
UPDATE "orders" AS "o" SET "doc" = jsonb_set("o"."doc", $1, $2, $3)
args: 3
UPDATE "orders" AS "o" SET "doc" = jsonb_set_lax("o"."doc", $1, NULL, $2, $3)
args: 3
UPDATE "orders" AS "o" SET "doc" = ("o"."doc" || $1)
args: 1
UPDATE "orders" AS "o" SET "doc" = ("o"."doc" #- $1)
args: 1
UPDATE "orders" AS "o" SET "doc"[$1][$2] = $3
args: 3
SELECT "o"."doc"[$1][$2][$3] FROM "orders" AS "o"
args: 3
Example (S6_03_flattenJSON)

### 6.3 Flatten arbitrary JSON to key paths (recursive walk)

docs := tbl("docs", "docs")
dID, doc := sqlb.Col[int64](docs, "id"), sqlb.Col[json.RawMessage](docs, "doc")
each, k, v := pg.Each(doc, "e")
seed := sel().Exprs(dID, sqlb.As(k, "key"), sqlb.As(v, "value"), sqlb.As(pg.ArrayOf(k), "path")).From(docs).Join(cross(each))

flat := tbl("flat", "f")
fID, fValue, fPath := ref[int64](flat, "id"), ref[json.RawMessage](flat, "value"), ref[[]string](flat, "path")
objEach, ok, ov := pg.Each(fValue, "oe")
objects := sel().Exprs(ok, ov).From(objEach).Where(sqlb.Eq(pg.JSONBTypeof(fValue), "object"))
arr := pg.WithOrdinality(pg.FuncSource(sqlb.Fn[json.RawMessage]("jsonb_array_elements", fValue), "a", "value", "ord"))
arrays := sel().Exprs(sqlb.Cast[string](sqlb.Sub(ref[int64](arr, "ord"), sqlb.Param[int64](1)), "text"), ref[json.RawMessage](arr, "value")).From(arr).
	Where(sqlb.Eq(pg.JSONBTypeof(fValue), "array"))
c := sqlb.Derived(objects.UnionAll(arrays), "c")
step := sel().Exprs(fID, ref[string](c, "key"), ref[json.RawMessage](c, "value"), pg.ArrayConcat(fPath, pg.ArrayOf(ref[string](c, "key")))).
	From(flat).Join(crossLateral(c))

show(sel().WithRecursive("flat", seed, step).
	Exprs(fID, sqlb.As(pg.ArrayToString(fPath, "."), "json_path"), fValue).From(flat).
	Where(sqlb.NotIn(pg.JSONBTypeof(fValue), "object", "array")).Build())
Output:
WITH RECURSIVE "flat" AS (SELECT "docs"."id", "e"."key" AS "key", "e"."value" AS "value", ARRAY["e"."key"] AS "path" FROM "docs" AS "docs" CROSS JOIN jsonb_each("docs"."doc") AS "e" ("key", "value") UNION ALL SELECT "f"."id", "c"."key", "c"."value", ("f"."path" || ARRAY["c"."key"]) FROM "flat" AS "f" CROSS JOIN LATERAL (SELECT "oe"."key", "oe"."value" FROM jsonb_each("f"."value") AS "oe" ("key", "value") WHERE (jsonb_typeof("f"."value") = $1) UNION ALL SELECT CAST(("a"."ord" - $2) AS text),
"a"."value" FROM jsonb_array_elements("f"."value") WITH ORDINALITY AS "a" ("value", "ord") WHERE (jsonb_typeof("f"."value") = $3)) AS "c")
SELECT "f"."id", array_to_string("f"."path", $4) AS "json_path", "f"."value" FROM "flat" AS "f" WHERE (jsonb_typeof("f"."value") NOT IN ($5, $6))
args: 6
Example (S6_04_jsonTable)

### 6.4 JSON_TABLE (PG17)

o := tbl("orders", "o")
oID, doc := sqlb.Col[int64](o, "id"), sqlb.Col[json.RawMessage](o, "doc")
jt := pg.JSONTable(doc, "$.items[*]", "jt",
	pg.JSONTableColumn{Name: "item_no", Ordinality: true},
	pg.JSONTableColumn{Name: "sku", Type: "text", Path: "$.sku"},
	pg.JSONTableColumn{Name: "qty", Type: "int", Path: "$.qty", DefaultOnEmpty: "0"},
	pg.JSONTableColumn{Name: "price", Type: "numeric", Path: "$.price", DefaultOnError: "0"},
	pg.JSONTableColumn{Nested: &pg.JSONTableNested{Path: "$.tags[*]", Columns: []pg.JSONTableColumn{{Name: "tag", Type: "text", Path: "$"}}}},
)
show(sel().Exprs(oID, ref[int64](jt, "item_no"), ref[string](jt, "sku"), ref[int32](jt, "qty"), ref[float64](jt, "price"), ref[*string](jt, "tag")).
	From(o).Join(cross(jt)).Build())
Output:
SELECT "o"."id", "jt"."item_no", "jt"."sku", "jt"."qty", "jt"."price", "jt"."tag" FROM "orders" AS "o" CROSS JOIN JSON_TABLE("o"."doc", '$.items[*]' COLUMNS ("item_no" FOR ORDINALITY, "sku" text PATH '$.sku', "qty" int PATH '$.qty' DEFAULT 0 ON EMPTY, "price" numeric PATH '$.price' DEFAULT 0 ON ERROR, NESTED PATH '$.tags[*]' COLUMNS ("tag" text PATH '$'))) AS "jt"
Example (S6_05_sqlJSON)

### 6.5 SQL/JSON constructors and predicates (PG16 constructors, PG17 query functions)

u := tbl("users", "u")
uID, uEmail, uPhone, uTag := sqlb.Col[int64](u, "id"), sqlb.Col[string](u, "email"), sqlb.Col[*string](u, "phone"), sqlb.Col[string](u, "tag")
show(sel().Exprs(
	pg.JSONObject([]pg.JSONPair{pg.Pair("id", uID), pg.Pair("email", uEmail), pg.Pair("phone", uPhone)}, pg.AbsentOnNull(), pg.WithUniqueKeys(), pg.ReturningJSONB()),
	pg.JSONArrayAgg(uTag, pg.JSONOrderBy(asc(uTag)), pg.AbsentOnNull()),
	pg.JSONObjectAgg(sqlb.Col[string](u, "k"), sqlb.Col[string](u, "v"), pg.AbsentOnNull()),
).From(u).Build())
o := tbl("orders", "o")
doc, rawText := sqlb.Col[json.RawMessage](o, "doc"), sqlb.Col[string](o, "raw_text")
show(sel().Exprs(sqlb.BoolExpr(pg.IsJSON(doc, pg.JSONObj, false)), sqlb.BoolExpr(pg.IsJSON(doc, pg.JSONArr, false)), sqlb.BoolExpr(pg.IsJSON(rawText, pg.JSONAny, true))).From(o).Build())
show(sel().Exprs(
	pg.JSONValue[int32](doc, "$.qty", pg.Returning("int"), pg.DefaultOnEmpty("0")),
	pg.JSONQuery(doc, "$.items[*].sku", pg.WithWrapper()),
	sqlb.BoolExpr(pg.JSONExists(doc, "$.customer.vip")),
).From(o).Build())
Output:
SELECT JSON_OBJECT(CAST($1 AS text): "u"."id", CAST($2 AS text): "u"."email", CAST($3 AS text): "u"."phone" ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb), JSON_ARRAYAGG("u"."tag" ORDER BY "u"."tag" ASC ABSENT ON NULL), JSON_OBJECTAGG("u"."k": "u"."v" ABSENT ON NULL) FROM "users" AS "u"
args: 3
SELECT "o"."doc" IS JSON OBJECT, "o"."doc" IS JSON ARRAY, "o"."raw_text" IS JSON WITH UNIQUE KEYS FROM "orders" AS "o"
SELECT JSON_VALUE("o"."doc", '$.qty' RETURNING int DEFAULT 0 ON EMPTY), JSON_QUERY("o"."doc", '$.items[*].sku' WITH WRAPPER), JSON_EXISTS("o"."doc", '$.customer.vip') FROM "orders" AS "o"
Example (S6_06_nestedDocument)

### 6.6 Nested API document in one query

c := tbl("customers", "c")
cID, cName := sqlb.Col[int64](c, "id"), sqlb.Col[string](c, "name")
o := tbl("orders", "o")
oID, oCustomer, oTotal, oCreated := sqlb.Col[int64](o, "id"), sqlb.Col[int64](o, "customer_id"), sqlb.Col[float64](o, "total"), sqlb.Col[time.Time](o, "created_at")
l := tbl("order_lines", "l")
lOrder, lLine := sqlb.Col[int64](l, "order_id"), sqlb.Col[int32](l, "line_no")
lRow := pg.RowRef(l)

items := sqlb.Derived(sel().Exprs(sqlb.As(pg.JSONBAgg(pg.DeleteKey(pg.ToJSONB(lRow), "order_id"), pg.OrderWithin(asc(lLine))), "items")).
	From(l).Where(sqlb.EqExpr(lOrder, oID)), "i")
emptyArr := pg.EmptyJSONArray()
orders := sqlb.Derived(sel().Exprs(sqlb.As(pg.JSONBAgg(
	pg.BuildObject(pg.Key("id"), oID, pg.Key("total"), oTotal, pg.Key("items"), sqlb.Coalesce(ref[json.RawMessage](items, "items"), emptyArr)),
	pg.OrderWithin(desc(oCreated))), "orders")).
	From(o).Join(leftLateral(items)).Where(sqlb.EqExpr(oCustomer, cID)), "o")
show(sel().Exprs(sqlb.As(pg.BuildObject(pg.Key("id"), cID, pg.Key("name"), cName, pg.Key("orders"), sqlb.Coalesce(ref[json.RawMessage](orders, "orders"), emptyArr)), "customer")).
	From(c).Join(leftLateral(orders)).Where(sqlb.Eq(cID, 7)).Build())
Output:
SELECT jsonb_build_object(CAST($1 AS text),
"c"."id", CAST($2 AS text),
"c"."name", CAST($3 AS text), COALESCE("o"."orders", '[]'::jsonb)) AS "customer" FROM "customers" AS "c" LEFT JOIN LATERAL (SELECT jsonb_agg(jsonb_build_object(CAST($4 AS text),
"o"."id", CAST($5 AS text),
"o"."total", CAST($6 AS text), COALESCE("i"."items", '[]'::jsonb)) ORDER BY "o"."created_at" DESC) AS "orders" FROM "orders" AS "o" LEFT JOIN LATERAL (SELECT jsonb_agg((to_jsonb("l") - $7) ORDER BY "l"."line_no" ASC) AS "items" FROM "order_lines" AS "l" WHERE ("l"."order_id" = "o"."id")) AS "i" ON (TRUE) WHERE ("o"."customer_id" = "c"."id")) AS "o" ON (TRUE) WHERE ("c"."id" = $8)
args: 8
Example (S6_08_jsonNullVersusSQLNull)

### 6.8 JSON null versus SQL NULL versus missing key

docs := tbl("docs", "docs")
doc := sqlb.Col[json.RawMessage](docs, "doc")
show(sel().Exprs(
	sqlb.As(sqlb.BoolExpr(sqlb.IsNull(pg.Get(doc, "x"))), "key_missing"),
	sqlb.As(sqlb.BoolExpr(sqlb.EqExpr(pg.Get(doc, "x"), pg.JSONNull())), "json_null"),
	sqlb.As(sqlb.BoolExpr(sqlb.IsNull(pg.JSONText(doc, "x"))), "missing_or_null"),
	sqlb.As(sqlb.BoolExpr(pg.HasKey(doc, "x")), "key_present"),
).From(docs).Build())
show(sel().Exprs(pg.StripNulls(sqlb.Param(json.RawMessage(`{"a": null, "b": {"c": null}}`)))).Build())
Output:
SELECT ("docs"."doc" -> $1) IS NULL AS "key_missing", ("docs"."doc" -> $2) = 'null'::jsonb AS "json_null", ("docs"."doc" ->> $3) IS NULL AS "missing_or_null", ("docs"."doc" ? $4) AS "key_present" FROM "docs" AS "docs"
args: 4
SELECT jsonb_strip_nulls($1)
args: 1
Example (S6_09_diffJSONB)

### 6.9 Diff two JSONB objects

a, aKey, aVal := pg.Each(sqlb.Param(json.RawMessage(`{"x":1}`)), "a")
b, _, bVal := pg.Each(sqlb.Param(json.RawMessage(`{"x":2}`)), "b")
show(sel().Exprs(aKey, sqlb.As(aVal, "old_value"), sqlb.As(bVal, "new_value")).From(a).
	Join(sqlb.Join{Type: pg.JoinFull, Source: b, Using: []string{"key"}}).
	Where(pg.IsDistinctFrom(aVal, bVal)).Build())
Output:
SELECT "a"."key", "a"."value" AS "old_value", "b"."value" AS "new_value" FROM jsonb_each($1) AS "a" ("key", "value") FULL JOIN jsonb_each($2) AS "b" ("key", "value") USING ("key") WHERE (("a"."value" IS DISTINCT FROM "b"."value"))
args: 2
Example (S6_10_updateArrayElement)

### 6.10 Update one element inside a JSON array by predicate

o := tbl("orders", "orders")
doc := sqlb.Col[json.RawMessage](o, "doc")
elems := pg.WithOrdinality(pg.FuncSource(sqlb.Fn[json.RawMessage]("jsonb_array_elements", pg.Get(doc, "items")), "t", "item", "ord"))
item, ord := ref[json.RawMessage](elems, "item"), ref[int64](elems, "ord")
rebuilt := sqlb.Case[json.RawMessage]().When(sqlb.Eq(pg.JSONText(item, "sku"), "A1"), pg.JSONConcat(item, sqlb.Param(json.RawMessage(`{"qty": 5}`)))).Else(item)
newItems := sel().Exprs(pg.JSONBAgg(rebuilt, pg.OrderWithin(asc(ord)))).From(elems)
show(sqlb.NewUpdate(d).Table(o).
	Set(sqlb.SetExpr(doc, pg.JSONBSetExpr(doc, []string{"items"}, sqlb.Subquery[json.RawMessage](newItems), true))).
	Where(pg.Contains(doc, json.RawMessage(`{"items": [{"sku": "A1"}]}`))).Build())
Output:
UPDATE "orders" AS "orders" SET "doc" = jsonb_set("orders"."doc", $1, (SELECT jsonb_agg(CASE WHEN ("t"."item" ->> $2) = $3 THEN ("t"."item" || $4) ELSE "t"."item" END ORDER BY "t"."ord" ASC) FROM jsonb_array_elements(("orders"."doc" -> $5)) WITH ORDINALITY AS "t" ("item", "ord")), $6) WHERE (("orders"."doc" @> $7))
args: 7
Example (S7_01_arrayFacts)

### 7.1 Facts that bite

empty := pg.EmptyArray[int32]("int")
withNull := pg.ArrayOf(sqlb.Literal[int32]("NULL"), sqlb.Param[int32](1))
three := pg.Array[int32](1, 2, 3)
show(sel().Exprs(
	pg.ArrayLength(empty, 1), pg.Cardinality(empty),
	sqlb.BoolExpr(pg.AnyOp(withNull, sqlb.OpEq, int32(1))), sqlb.BoolExpr(pg.AnyOp(withNull, sqlb.OpEq, int32(2))),
	sqlb.BoolExpr(pg.AllOp(withNull, sqlb.OpNotEq, int32(2))),
	pg.Subscript(pg.ArrayLit[string]("[0:1]={a,b}", "text"), 1),
	pg.SliceFrom(three, 2), pg.SliceTo(three, 2),
).Build())
Output:
SELECT array_length('{}'::int[], $1), cardinality('{}'::int[]), $2 = ANY(ARRAY[NULL, $3]), $4 = ANY(ARRAY[NULL, $5]), $6 <> ALL(ARRAY[NULL, $7]), '[0:1]={a,b}'::text[][$8], (ARRAY[CAST($9 AS int), CAST($10 AS int), CAST($11 AS int)])[$12:], (ARRAY[CAST($13 AS int), CAST($14 AS int), CAST($15 AS int)])[:$16]
args: 16
Example (S7_02_dedupPreservingOrder)

### 7.2 Dedup preserving first-seen order

src := pg.WithOrdinality(pg.FuncSource(sqlb.Fn[int32]("unnest", sqlb.Cast[[]int32](sqlb.Param([]int32{3, 1, 3}), "int[]")), "t", "x", "ord"))
x, ord := ref[int32](src, "x"), ref[int64](src, "ord")
first := sqlb.Derived(sel().Clause(pg.DistinctOn(x)).Exprs(x, ord).From(src).Order(asc(x), asc(ord)), "s")
show(sel().Exprs(pg.ArrayAgg(ref[int32](first, "x"), pg.OrderWithin(asc(ref[int64](first, "ord"))))).From(first).Build())
Output:
SELECT array_agg("s"."x" ORDER BY "s"."ord" ASC) FROM (SELECT DISTINCT ON ("t"."x") "t"."x", "t"."ord" FROM unnest(CAST($1 AS int[])) WITH ORDINALITY AS "t" ("x", "ord") ORDER BY "t"."x" ASC, "t"."ord" ASC) AS "s"
args: 1
Example (S7_03_arraySetOperations)

### 7.3 Set operations on arrays

v := pg.Values("v", []string{"a", "b"}, []sqlb.SQLer{sqlb.Literal[[]int32]("ARRAY[1,2,3,3,NULL]"), sqlb.Literal[[]int32]("ARRAY[3,4]")})
a, b := ref[[]int32](v, "a"), ref[[]int32](v, "b")
un := func(arr sqlb.Expr[[]int32]) *sqlb.Select[sqlb.PostgresDialect] {
	return sel().Exprs(pg.UnnestScalar(arr))
}
show(sel().Exprs(
	sqlb.As(pg.ArraySubquery[int32](un(a).Intersect(un(b))), "common"),
	sqlb.As(pg.ArraySubquery[int32](un(a).Except(un(b))), "only_in_a"),
	sqlb.As(sqlb.BoolExpr(pg.OverlapsExpr(a, b)), "overlaps"),
	sqlb.As(sqlb.BoolExpr(pg.ArrayContainsExpr(a, b)), "a_contains_b"),
	sqlb.As(sqlb.BoolExpr(pg.ArrayContainedByExpr(a, b)), "a_within_b"),
	sqlb.As(pg.ArrayRemoveNull(a), "without_nulls"),
	pg.ArrayReplace(a, 2, 20), pg.ArrayPosition(a, 3), pg.ArrayPositions(a, 3),
).From(v).Build())
Output:
SELECT ARRAY(SELECT unnest("v"."a") INTERSECT SELECT unnest("v"."b")) AS "common", ARRAY(SELECT unnest("v"."a") EXCEPT SELECT unnest("v"."b")) AS "only_in_a", ("v"."a" && "v"."b") AS "overlaps", ("v"."a" @> "v"."b") AS "a_contains_b", ("v"."a" <@ "v"."b") AS "a_within_b", array_remove("v"."a", NULL) AS "without_nulls", array_replace("v"."a", $1, $2), array_position("v"."a", $3), array_positions("v"."a", $4) FROM (VALUES (ARRAY[1,2,3,3,NULL], ARRAY[3,4])) AS "v" ("a", "b")
args: 4
Example (S7_04_arrayWithDuplicates)

### 7.4 Rows whose array contains duplicates

t := tbl("t", "t")
tags := sqlb.Col[[]string](t, "tags")
un, x := pg.Unnest(tags, "x")
distinct := sel().Exprs(sqlb.CountOf(x, sqlb.Distinct())).From(un)
show(sel().Exprs(tags).From(t).Where(sqlb.NotEqExpr(sqlb.Cast[int64](pg.Cardinality(tags), "bigint"), sqlb.AssertNotNull(sqlb.Subquery[int64](distinct)))).Build())
Output:
SELECT "t"."tags" FROM "t" AS "t" WHERE (CAST(cardinality("t"."tags") AS bigint) <> (SELECT count(DISTINCT "x"."elem") FROM unnest("t"."tags") AS "x" ("elem")))
Example (S7_05_arrayParameters)

### 7.5 Arrays as parameters instead of IN lists

u := tbl("users", "users")
id := sqlb.Col[int32](u, "id")
show(sel().Exprs(id).From(u).Where(pg.InArray(id, []int32{1, 2, 3})).Build())
show(sel().Exprs(id).From(u).Where(pg.AllOp(sqlb.Param([]int32{1, 2}), sqlb.OpNotEq, int32(0)), sqlb.IsNotNull(id)).Build())
// The second form is "id <> ALL ($1)": pg.AllOp puts the value on the left, so the
// snippet's exact spelling uses the typed door.
show(sel().Exprs(id).From(u).Where(pg.AllExpr(id, sqlb.OpNotEq, sqlb.Param([]int32{1, 2})), sqlb.IsNotNull(id)).Build())
Output:
SELECT "users"."id" FROM "users" AS "users" WHERE ("users"."id" = ANY($1))
args: 1
SELECT "users"."id" FROM "users" AS "users" WHERE ($1 <> ALL($2) AND "users"."id" IS NOT NULL)
args: 2
SELECT "users"."id" FROM "users" AS "users" WHERE ("users"."id" <> ALL($1) AND "users"."id" IS NOT NULL)
args: 1
Example (S7_06_indexingArrays)

### 7.6 Indexing arrays CREATE INDEX is DDL. The two SELECTs show the operator the GIN index serves.

p := tbl("posts", "posts")
tags := sqlb.Col[[]string](p, "tags")
show(sel().Exprs(tags).From(p).Where(pg.ArrayContains(tags, []string{"postgres"})).Build())
show(sel().Exprs(tags).From(p).Where(pg.Any(tags, "postgres")).Build())
Output:
SELECT "posts"."tags" FROM "posts" AS "posts" WHERE (("posts"."tags" @> $1))
args: 1
SELECT "posts"."tags" FROM "posts" AS "posts" WHERE ($1 = ANY("posts"."tags"))
args: 1
Example (S7_07_jsonArrayToTypedArray)

### 7.7 JSON array to typed array and back

elems, el := pg.ArrayElementsText(sqlb.Param(json.RawMessage(`[1,2,3]`)), "e")
show(sel().Exprs(pg.ArraySubquery[int32](sel().Exprs(sqlb.Cast[int32](el, "int")).From(elems))).Build())
show(sel().Exprs(pg.ToJSONB(pg.Array(1, 2, 3))).Build())
show(sel().Exprs(pg.StringToArrayNull(sqlb.Param("a,,b"), ",", "")).Build())
parts, part := pg.StringToTable(sqlb.Param("a b c"), " ", "p")
show(sel().Exprs(part).From(parts).Build())
Output:
SELECT ARRAY(SELECT CAST("e"."value" AS int) FROM jsonb_array_elements_text($1) AS "e" ("value"))
args: 1
SELECT to_jsonb(ARRAY[CAST($1 AS bigint), CAST($2 AS bigint), CAST($3 AS bigint)])
args: 3
SELECT string_to_array($1, $2, $3)
args: 3
SELECT "p"."part" FROM string_to_table($1, $2) AS "p" ("part")
args: 2
Example (S8_02_rangeBounds)

### 8.2 Range bound gotchas

show(sel().Exprs(
	pg.Int8Range(1, 5, pg.BoundsIncInc),
	pg.DateRange(sqlb.Param(t0), sqlb.Param(t1), pg.BoundsIncInc),
	pg.NumRange(sqlb.Param(1.0), sqlb.Param(5.0), pg.BoundsIncInc),
	pg.TstzRangeExpr(pg.NullBound(), pg.Now(), pg.BoundsIncExc),
	sqlb.BoolExpr(pg.IsEmpty(pg.Int8Range(5, 5, pg.BoundsIncExc))),
	sqlb.BoolExpr(pg.RangeContains(pg.Int8Range(5, 5, pg.BoundsIncExc), 5)),
	sqlb.BoolExpr(pg.UpperInf(pg.TstzRangeExpr(pg.Now(), pg.NullBound(), pg.BoundsIncExc))),
).Build())
Output:
SELECT int8range($1, $2, '[]'), daterange($3, $4, '[]'), numrange($5, $6, '[]'), tstzrange(NULL, now(), '[)'), isempty(int8range($7, $8, '[)')), (int8range($9, $10, '[)') @> CAST($11 AS bigint)), upper_inf(tstzrange(now(), NULL, '[)'))
args: 11
Example (S8_03_freeSlots)

### 8.3 Free slots via multirange subtraction (PG14)

b := tbl("bookings", "bookings")
room, during, status := sqlb.Col[int64](b, "room_id"), sqlb.Col[pg.Range[time.Time]](b, "during"), sqlb.Col[string](b, "status")
window := pg.TstzRange(t0, t1, pg.BoundsIncExc)
free := pg.UnnestMultirange(pg.MultirangeDiff(pg.TstzMultirange(window), pg.RangeAggTyped(during)))
show(sel().Exprs(sqlb.As(free, "free_slot")).From(b).
	Where(sqlb.Eq(room, 3), sqlb.Eq(status, "active"), pg.RangeOverlaps(during, window)).Build())
Output:
SELECT unnest((tstzmultirange(tstzrange($1, $2, '[)')) - range_agg("bookings"."during"))) AS "free_slot" FROM "bookings" AS "bookings" WHERE ("bookings"."room_id" = $3 AND "bookings"."status" = $4 AND ("bookings"."during" && tstzrange($5, $6, '[)')))
args: 6
Example (S8_04_mergeIntervals)

### 8.4 Merge overlapping intervals without multiranges

iv := tbl("intervals", "intervals")
id, s, e := sqlb.Col[int64](iv, "id"), sqlb.Col[time.Time](iv, "s"), sqlb.Col[time.Time](iv, "e")
prevMax := sqlb.Over(sqlb.Max(e), sqlb.NewWindow().PartitionBy(id).OrderBy(asc(s), asc(e)).Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.Preceding(1)))
newGrp := sqlb.Case[int32]().When(sqlb.GreaterExpr(s, prevMax), sqlb.Param[int32](1)).Else(sqlb.Param[int32](0))
x := sqlb.Derived(sel().Exprs(id, s, e, sqlb.As(newGrp, "new_grp")).From(iv), "x")
xID, xS, xE := ref[int64](x, "id"), ref[time.Time](x, "s"), ref[time.Time](x, "e")
grp := sqlb.Over(sqlb.Sum(ref[int32](x, "new_grp")), sqlb.NewWindow().PartitionBy(xID).OrderBy(asc(xS), asc(xE)).Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.CurrentRow()))
y := sqlb.Derived(sel().Exprs(xID, xS, xE, sqlb.As(grp, "grp")).From(x), "y")
yID, yS, yE, yGrp := ref[int64](y, "id"), ref[time.Time](y, "s"), ref[time.Time](y, "e"), ref[int32](y, "grp")
show(sel().Exprs(yID, sqlb.As(sqlb.Min(yS), "merged_start"), sqlb.As(sqlb.Max(yE), "merged_end")).From(y).
	GroupBy(yID, yGrp).Order(asc(yID), asc(sqlb.OutputCol[time.Time]("merged_start"))).Build())
Output:
SELECT "y"."id", min("y"."s") AS "merged_start", max("y"."e") AS "merged_end" FROM (SELECT "x"."id", "x"."s", "x"."e", sum("x"."new_grp") OVER (PARTITION BY "x"."id" ORDER BY "x"."s" ASC, "x"."e" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "grp" FROM (SELECT "intervals"."id", "intervals"."s", "intervals"."e", CASE WHEN "intervals"."s" > max("intervals"."e") OVER (PARTITION BY "intervals"."id" ORDER BY "intervals"."s" ASC, "intervals"."e" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN $1 ELSE $2 END AS "new_grp" FROM "intervals" AS "intervals") AS "x") AS "y" GROUP BY "y"."id", "y"."grp" ORDER BY "y"."id" ASC, "merged_start" ASC
args: 2
Example (S8_05_sweepLine)

### 8.5 Maximum concurrency (sweep line)

sess := tbl("sessions", "sessions")
start, end := sqlb.Col[time.Time](sess, "start_ts"), sqlb.Col[time.Time](sess, "end_ts")
starts := sel().Exprs(sqlb.As(start, "ts"), sqlb.As(sqlb.Param[int32](1), "delta")).From(sess)
ends := sel().Exprs(end, sqlb.Param[int32](-1)).From(sess)
ev := sqlb.Derived(starts.UnionAll(ends), "ev")
ts, delta := ref[time.Time](ev, "ts"), ref[int32](ev, "delta")
running := sqlb.Over(sqlb.Sum(delta), sqlb.NewWindow().OrderBy(asc(ts), asc(delta)).Frame(sqlb.FrameRows, sqlb.UnboundedPreceding(), sqlb.CurrentRow()))
s := sqlb.Derived(sel().Exprs(ts, sqlb.As(running, "running")).From(ev), "s")
show(sel().Exprs(ref[time.Time](s, "ts"), sqlb.As(ref[int32](s, "running"), "concurrent")).From(s).
	Order(desc(ref[int32](s, "running")), asc(ref[time.Time](s, "ts"))).Limit(1).Build())
Output:
SELECT "s"."ts", "s"."running" AS "concurrent" FROM (SELECT "ev"."ts", sum("ev"."delta") OVER (ORDER BY "ev"."ts" ASC, "ev"."delta" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "running" FROM (SELECT "sessions"."start_ts" AS "ts", $1 AS "delta" FROM "sessions" AS "sessions" UNION ALL SELECT "sessions"."end_ts", $2 FROM "sessions" AS "sessions") AS "ev") AS "s" ORDER BY "s"."running" DESC, "s"."ts" ASC LIMIT $3
args: 3
Example (S8_06_slowlyChangingDimension)

### 8.6 Slowly changing dimension (type 2) with an as-of query The CREATE TABLE is DDL. The close-and-open statement and the two reads render.

ph := tbl("price_history", "price_history")
product, price, valid := sqlb.Col[int64](ph, "product_id"), sqlb.Col[float64](ph, "price"), sqlb.Col[pg.Range[time.Time]](ph, "valid")
closed := sqlb.UpdateReturning(sqlb.NewUpdate(d).Table(ph).
	Set(sqlb.SetExpr(valid, pg.TstzRangeExpr(pg.LowerBound(valid), pg.Now(), pg.BoundsIncExc))).
	Where(sqlb.Eq(product, 7), pg.UpperInf(valid)), sqlb.Bind(product, &sink.i64))
show(sqlb.NewInsert(d).With("closed", closed).Into(ph).Columns(product, price).
	Select(sel().Exprs(sqlb.Param[int64](7), sqlb.Param(9.5))).Build())
show(sel().Exprs(price).From(ph).Where(sqlb.Eq(product, 7), pg.RangeContains(valid, t1)).Build())
show(sel().Exprs(price).From(ph).Where(sqlb.Eq(product, 7), pg.UpperInf(valid)).Build())
Output:
WITH "closed" AS (UPDATE "price_history" AS "price_history" SET "valid" = tstzrange(lower("price_history"."valid"), now(), '[)') WHERE ("price_history"."product_id" = $1 AND upper_inf("price_history"."valid")) RETURNING "price_history"."product_id") INSERT INTO "price_history" AS "price_history" ("product_id", "price")
SELECT $2, $3
args: 3
SELECT "price_history"."price" FROM "price_history" AS "price_history" WHERE ("price_history"."product_id" = $1 AND ("price_history"."valid" @> CAST($2 AS timestamptz)))
args: 2
SELECT "price_history"."price" FROM "price_history" AS "price_history" WHERE ("price_history"."product_id" = $1 AND upper_inf("price_history"."valid"))
args: 1
Example (S8_08_gapsBetweenBookings)

### 8.8 Gaps between consecutive bookings

b := tbl("bookings", "bookings")
room, during := sqlb.Col[int64](b, "room_id"), sqlb.Col[pg.Range[time.Time]](b, "during")
prev := pg.Lag(during, 1).Over(sqlb.NewWindow().PartitionBy(room).OrderBy(asc(during)))
s := sqlb.Derived(sel().Exprs(room, during, sqlb.As(prev, "prev")).From(b), "s")
sPrev, sDuring := sqlb.AssertNotNull(ref[*pg.Range[time.Time]](s, "prev")), ref[pg.Range[time.Time]](s, "during")
show(sel().Exprs(ref[int64](s, "room_id"), sqlb.As(pg.TstzRangeExpr(pg.UpperBound(sPrev), pg.LowerBound(sDuring), pg.BoundsIncExc), "gap")).
	From(s).Where(sqlb.IsNotNull(ref[*pg.Range[time.Time]](s, "prev")), sqlb.LessExpr(pg.UpperBound(sPrev), pg.LowerBound(sDuring))).Build())
Output:
SELECT "s"."room_id", tstzrange(upper("s"."prev"), lower("s"."during"), '[)') AS "gap" FROM (SELECT "bookings"."room_id", "bookings"."during", lag("bookings"."during", $1) OVER (PARTITION BY "bookings"."room_id" ORDER BY "bookings"."during" ASC) AS "prev" FROM "bookings" AS "bookings") AS "s" WHERE ("s"."prev" IS NOT NULL AND upper("s"."prev") < lower("s"."during"))
args: 1
Example (S9_01_weightedSearch)

### 9.1 Weighted generated tsvector with GIN, ranking and highlighting ALTER TABLE ... GENERATED and CREATE INDEX are DDL. The search renders.

a := tbl("articles", "articles")
aID, title, body, search := sqlb.Col[int64](a, "id"), sqlb.Col[string](a, "title"), sqlb.Col[string](a, "body"), sqlb.Col[pg.TSVector](a, "search")
qSrc := pg.FuncSource(pg.WebsearchToTSQuery("english", `postgres -mysql "window function"`), "q")
q := ref[pg.TSQuery](qSrc, "q")
show(sel().Exprs(aID, title,
	sqlb.As(pg.TSRankCDNormalized(search, q, 32), "rank"),
	sqlb.As(pg.TSHeadline("english", body, q, "MaxFragments=2, MinWords=5, MaxWords=15, StartSel=<b>, StopSel=</b>"), "snippet")).
	From(a).Join(cross(qSrc)).Where(pg.Matches(search, q)).Order(desc(sqlb.OutputCol[float32]("rank"))).Limit(10).Build())
Output:
SELECT "articles"."id", "articles"."title", ts_rank_cd("articles"."search", "q"."q", $1) AS "rank", ts_headline('english', "articles"."body", "q"."q", $2) AS "snippet" FROM "articles" AS "articles" CROSS JOIN websearch_to_tsquery('english', $3) AS "q" WHERE (("articles"."search" @@ "q"."q")) ORDER BY "rank" DESC LIMIT $4
args: 4
Example (S9_02_prefixPhraseDistance)

### 9.2 Prefix, phrase, and distance queries

fox := pg.ToTSVector("english", sqlb.Param("the quick brown fox"))
show(sel().Exprs(pg.ToTSQuery("english", "postg:*")).Build())
show(sel().Exprs(sqlb.BoolExpr(pg.Matches(fox, pg.PhraseToTSQuery("english", "quick fox")))).Build())
show(sel().Exprs(sqlb.BoolExpr(pg.Matches(fox, pg.ToTSQuery("english", "quick <2> fox")))).Build())
Output:
SELECT to_tsquery('english', $1)
args: 1
SELECT (to_tsvector('english', $1) @@ phraseto_tsquery('english', $2))
args: 2
SELECT (to_tsvector('english', $1) @@ to_tsquery('english', $2))
args: 2
Example (S9_03_accentInsensitive)

### 9.3 Accent-insensitive search config CREATE EXTENSION and CREATE TEXT SEARCH CONFIGURATION are DDL. The test query renders.

show(sel().Exprs(sqlb.BoolExpr(pg.Matches(pg.ToTSVector("english_unaccent", sqlb.Param("Crème brûlée")), pg.ToTSQuery("english_unaccent", "creme")))).Build())
Output:
SELECT (to_tsvector('english_unaccent', $1) @@ to_tsquery('english_unaccent', $2))
args: 2
Example (S9_04_trigram)

### 9.4 Trigram fuzzy match and substring LIKE that uses an index CREATE EXTENSION and the two indexes are DDL. The searches render.

u := tbl("users", "users")
name, email := sqlb.Col[string](u, "name"), sqlb.Col[string](u, "email")
needle := sqlb.Param("jon smith")
show(sel().Exprs(name, sqlb.As(trgm.Similarity(name, needle), "sim")).From(u).
	Where(trgm.Similar(name, needle)).Order(asc(trgm.Distance(name, needle))).Limit(10).Build())
show(sel().Exprs(email).From(u).Where(pg.ILike(email, "%@example.%")).Build())
show(sel().Exprs(trgm.WordSimilarity(sqlb.Param("smith"), sqlb.Param("john smithers"))).Build())
Output:
SELECT "users"."name", similarity("users"."name", $1) AS "sim" FROM "users" AS "users" WHERE (("users"."name" % $2)) ORDER BY ("users"."name" <-> $3) ASC LIMIT $4
args: 4
SELECT "users"."email" FROM "users" AS "users" WHERE (("users"."email" ILIKE $1))
args: 1
SELECT word_similarity($1, $2)
args: 2
Example (S9_05_phonetic)

### 9.5 Phonetic and edit-distance matching

u := tbl("users", "users")
name := sqlb.Col[string](u, "name")
show(sel().Exprs(name).From(u).Where(sqlb.EqExpr(fuzzy.Dmetaphone(name), fuzzy.Dmetaphone(sqlb.Param("Katherine")))).Build())
show(sel().Exprs(name).From(u).Where(sqlb.LessEq(fuzzy.LevenshteinLessEqual(sqlb.Lower(name), sqlb.Param("jonathon"), 2), 2)).Build())
Output:
SELECT "users"."name" FROM "users" AS "users" WHERE (dmetaphone("users"."name") = dmetaphone($1))
args: 1
SELECT "users"."name" FROM "users" AS "users" WHERE (levenshtein_less_equal(lower("users"."name"), $1, $2) <= $3)
args: 3
Example (S9_06_probableDuplicates)

### 9.6 Probable-duplicate customers

a, b := tbl("customers", "a"), tbl("customers", "b")
aID, aName, aEmail := sqlb.Col[int64](a, "id"), sqlb.Col[string](a, "name"), sqlb.Col[string](a, "email")
bID, bName, bEmail := sqlb.Col[int64](b, "id"), sqlb.Col[string](b, "name"), sqlb.Col[string](b, "email")
show(sel().Exprs(aID, bID, aName, bName, sqlb.As(trgm.Similarity(aName, bName), "sim")).From(a).
	Join(inner(b, sqlb.LessExpr(aID, bID), trgm.Similar(aName, bName))).
	Where(pg.IsDistinctFrom(aEmail, bEmail)).Order(desc(sqlb.OutputCol[float32]("sim"))).Build())
Output:
SELECT "a"."id", "b"."id", "a"."name", "b"."name", similarity("a"."name", "b"."name") AS "sim" FROM "customers" AS "a" INNER JOIN "customers" AS "b" ON ("a"."id" < "b"."id" AND ("a"."name" % "b"."name")) WHERE (("a"."email" IS DISTINCT FROM "b"."email")) ORDER BY "sim" DESC

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL