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 (S14_10_dblink) ¶
### 14.10 Autonomous transaction via dblink (the call)
show(sel().Exprs(sqlb.Fn[string]("dblink_exec", sqlb.Concat(sqlb.Param("dbname="), pg.CurrentDatabase()),
pg.Format("INSERT INTO attempt_log (msg, at) VALUES (%L, now())", sqlb.Param("about to try")))).Build())
Output: SELECT dblink_exec(concat(CAST($1 AS text), current_database()), format($2, $3)) args: 3
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