PartiQL: keyword reference

The flat index for the dialect series: every keyword, operator, and function the editor understands, one sample line each, tagged with where it executes. The guide pages teach these in context — this page is the cheat-sheet you keep open while you write.

Tags follow the series vocabulary: native (DynamoDB runs it server-side), lowered (the studio translates it; client-side work is disclosed), Kanject (a convention extension beyond the PartiQL spec). Where a keyword behaves differently by position — a predicate that narrows a key versus one that only filters — the entry says so.

Each core statement also has a dedicated page with syntax, use-cases, and quotable, deep-linkable examples:

Statements

SELECT  * FROM "stage.Users" WHERE pk = 'User#9'INSERT  INTO "stage.Users" VALUE {'pk': 'User#9', 'sk': 'PROFILE'}INSERT  … VALUE {…} ON CONFLICT DO NOTHING | DO REPLACE | DO UPDATE SET …UPDATE  "stage.Users" SET Role = 'admin' WHERE pk = 'User#9' AND sk = 'PROFILE'DELETE  FROM "stage.Users" WHERE pk = 'User#9' AND sk = 'PROFILE'PROFILE TABLE "stage.Users"BEGIN TRANSACTION; … ; COMMIT;

• SELECT — native / lowered. Reads items; projection, DISTINCT, joins, subqueries, set operators and aggregates layer on top. Read paths land in First queries and Targeted reads.
• SELECT VALUE expr — lowered. Each row is the evaluated value; a tuple spreads into columns, anything else lands in a value column. First queries.
• INSERT INTO … VALUE {…} — native. Writes one item from a PartiQL object literal; inserting over an existing key is refused, never an overwrite. See Writes & transactions.
• INSERT … ON CONFLICT DO NOTHING / DO REPLACE / DO UPDATE SET … — lowered. Resolves a primary-key collision in one native write: skip (attribute_not_exists PutItem), overwrite (PutItem), or upsert (UpdateItem). See Writes & transactions.
• UPDATE … SET … WHERE … — native. Single-item write when the WHERE names the complete primary key; a looser WHERE runs as a preview-first set-based write. Writes & transactions.
• DELETE FROM … WHERE … — native. Single-item delete under the complete-key rule, or a preview-first set-based delete on a looser WHERE. Writes & transactions.
• PROFILE TABLE — lowered. Samples a table and reports per-attribute coverage and type distribution. Aggregates & profiling.
• BEGIN TRANSACTION / COMMIT — native. Wraps a write script into one all-or-nothing transaction. Writes & transactions.

Schema & DDL

CREATE TABLE "stage.Ledger" (PARTITION KEY pk STRING, SORT KEY sk STRING) WITH BILLING = ON_DEMANDDROP   TABLE "stage.Ledger"                          -- type-the-name confirmationCREATE GSI "byRegionStatus" ON "stage.Orders"        -- up to 4 partition + 4 sort keys  PARTITION KEY (region, tenant) SORT KEY (status, createdAt) PROJECT ALLCREATE ANALYTICS REPLICA FOR TABLE "stage.Orders"    -- S3 Tables zero-ETL integrationCREATE UNIQUE     INDEX "uxTenantEmail" ON "stage.Users" (tenantId, email)  -- Kanject markerCREATE UNIQUE     INDEX ON "stage.Users" (email) WHERE status = 'active'    -- partial markerCREATE COLLECTION INDEX ON "stage.Orders" (status)   -- membership marker (one row per item)CREATE RANGE      INDEX ON "stage.Scores" (points)   -- range-scannable markerALTER  UNIQUE     INDEX ON "stage.Users" (email) REFRESH   -- reconcile reserved rowsDROP   UNIQUE     INDEX ON "stage.Users" (email)           -- delete the reserved rows

DynamoDB's own PartiQL has no DDL — these are a DynoStudio dialect surface that lowers to the control-plane calls the AWS services actually use, disclosed in the "Executes as" strip before it runs. A read-only stage refuses all mutating control-plane work.

• CREATE TABLE … WITH BILLING = ON_DEMAND | PROVISIONED (RCU n, WCU n) — lowered. Becomes a CreateTable; each key attribute's type is required — STRING, NUMBER, or BINARY (DynamoDB fixes key types at creation, so the studio won't guess one).
• DROP TABLE / DELETE TABLE — lowered. Becomes a DeleteTable, and is irreversible — like the AWS Console, the run stays disarmed until you type the exact table name.
• CREATE GSI / CREATE GLOBAL SECONDARY INDEX / CREATE INDEX … PARTITION KEY (…) [SORT KEY (…)] PROJECT ALL | KEYS | INCLUDE (…) — lowered. Becomes an UpdateTable; supports DynamoDB multi-key GSIs (up to 4 partition + 4 sort attributes) and the service auto-backfills the index from existing attributes.
• CREATE / ALTER / DROP ANALYTICS REPLICA FOR TABLE "T" — lowered. Provisions, reconfigures or removes a DynamoDB to Amazon S3 Tables analytics replica. The create path expands to the ordered DynamoDB, S3 Tables, Lake Formation, Glue and IAM setup plan, with native calls, likely permissions and recurring costs previewed before consent. See Tables, indexes & replicas.
• CREATE UNIQUE INDEX ["name"] ON "T" (col[, col…]) — Kanject. Backfills a uniqueness marker as reserved rows in the base table: scan, group by value, and refuse with the colliding items listed rather than pick a winner; clean data writes one reserved row per value. A multi-column (composite) unique needs a name matching the entity's [CompositeUnique("name")] group.
• CREATE COLLECTION INDEX / CREATE RANGE INDEX — Kanject. Membership markers that write one reserved row per item (no uniqueness check): COLLECTION buckets items by the value, RANGE pools them into one value-sorted bucket for range scans. Both need the base table to have a sort key.
• … INDEX … WHERE … (partial marker) — Kanject. A trailing WHERE indexes only the matching rows — a partial UNIQUE only conflicts within that scope. The predicate is applied per scanned item during the backfill; an unparseable one refuses rather than index everything.
• ALTER … INDEX … REFRESH / … SET WHERE … · DROP … INDEX … — Kanject. REFRESH reconciles the reserved rows to current data (a UNIQUE refresh can surface new conflicts and refuse, like create); SET WHERE re-scopes a partial marker; DROP deletes the reserved rows. The lifecycle is create → alter → drop.

Markers work in conjunction with the Kanject Core library: DynoStudio backfills the reserved rows, and the matching annotation ([Unique], [Collection], …) is what enforces the constraint on every future write. So a backfill nudges you to add that annotation — the backfill alone is point-in-time. Runs from the PartiQL console and Browse's PartiQL mode; the in-app path is for bounded tables, larger ones route to an AWS Glue job.

Clauses

FROM "stage.Users"."ByOrg"          -- base table or "table"."index"WHERE OrgId = 'Org#kanject'            -- filter / key conditionGROUP BY Status                     -- fold matches into groupsORDER BY Age DESC NULLS LAST        -- sort (ASC | DESC, NULLS FIRST | LAST)LIMIT 50 OFFSET 10                  -- page windowRETURNING ALL OLD *                 -- echo the written itemJOIN "stage.Customers" AS c ON c.pk = 'Customer#{CustomerId}'

• FROM "table" / FROM "table"."index" — native. Reads the base table or a GSI — including multi-key GSIs (up to 4 partition + 4 sort attributes), where a Query needs equality on every partition attribute, then the sort attributes bound left-to-right with no gaps and at most one trailing inequality (begins_with last); a gap or a second range degrades to a Scan. Quote namespaced names containing dots. Targeted reads.
• WHERE — native. Predicates either form the key condition (bounding what DynamoDB reads) or filter after the read. The split is the whole of Targeted reads.
• GROUP BY — lowered. One result row per distinct key combination, folded client-side. Aggregates & profiling.
• ORDER BY (ASC / DESC, NULLS FIRST / NULLS LAST) — native or lowered. The queried sort key stays server-side; anything else sorts each page client-side. Targeted reads.
• LIMIT — native. Sent as the request page limit; with GROUP BY it bounds the groups returned.
• OFFSET — lowered. Skipped rows are fetched, dropped client-side, and still billed. First page only.
• RETURNING ALL OLD * / ALL NEW * — native. Echoes the item before or after a single-statement write (not inside transactions).
• JOIN / LEFT / RIGHT / FULL [OUTER] JOIN … ON … (with AS aliases) — lowered. INNER/LEFT resolve per left row through the right side's key or a GSI; RIGHT/FULL drain the whole right side (a capped Scan) and join in memory, keeping unmatched rows from the outer side(s). Joins, subqueries & sets.
• CROSS JOIN (no ON) — lowered. The Cartesian product of both fully-drained sides, capped at 10,000 output rows. Joins, subqueries & sets.
• Two or more JOINs (a chain) — lowered. The base table drains, then each table resolves per accumulated row through its key, rows threaded alias-qualified. INNER and LEFT joins chain; ON is attribute-equality or a Kanject key template, binding each step's base key or a GSI key. A RIGHT / FULL / CROSS join inside a chain refuses. Joins, subqueries & sets.
• SET / REMOVE — native. UPDATE assignment forms — repeated SET to assign, REMOVE to drop an attribute.
• AS — lowered. Renames a projected column or aliases a FROM/JOIN source; the rename happens client-side per item.

Operators

WHERE Age >= 18 AND Country <> 'US'          -- comparisons + ANDWHERE Score BETWEEN 10 AND 20                -- rangeWHERE pk IN ('User#1', 'User#2')             -- membershipWHERE pk IN (SELECT 'O#{Id}' FROM "stage.B")     -- semi-join (subquery)WHERE sk NOT IN (SELECT 'E#{Id}' FROM "stage.P") -- anti-join (subquery)WHERE Status = 'open' OR NOT Archived        -- OR / NOT (forces Scan)WHERE Nickname IS MISSING                    -- attribute absenceSELECT DISTINCT Country FROM "stage.Users"   -- de-duplicateSELECT 'Outbox#' || Id FROM "stage.Outbox"   -- string concat (||, not +)

• = <> != < <= > >= — native. Partition-key equality forms the key condition; sort-key comparisons on a queried target narrow it; anything else filters.
• BETWEEN low AND high — native. Narrows the key condition on a queried sort key; elsewhere it filters.
• IN (…) — native. On the partition key, one key lookup per value; never on a sort key; elsewhere it filters.
• IN (SELECT …) — lowered. A semi-join: the subquery runs first, then the outer read (inlined as key lookups when small, else a page filter). Joins, subqueries & sets.
• NOT IN (SELECT …) — lowered. The anti-join mirror — keeps rows absent from the subquery set; no key-lookup form, so the outer read Scans unless another predicate keys it. Joins, subqueries & sets.
• AND — native. Composes key conditions and filters freely.
• OR / NOT — native (filter only). Can never form a key condition; around key predicates they degrade the read to a Scan, and the editor says so.
• IS MISSING / IS NOT MISSING — native. Tests attribute absence — not the same as an explicit NULL.
• DISTINCT — lowered. De-duplicates each fetched page by deep value equality; duplicates across page boundaries can reappear.
• || — lowered. String concatenation in a projection, computed client-side per item (like AS). Works the same in a top-level SELECT and in a subquery's inner projection — ||, never +. Semi-join use in Joins, subqueries & sets.

Set operators

SELECT * FROM "stage.A" UNION     SELECT * FROM "stage.B"SELECT * FROM "stage.A" UNION ALL SELECT * FROM "stage.B"SELECT * FROM "stage.A" INTERSECT SELECT * FROM "stage.B"SELECT * FROM "stage.A" EXCEPT    SELECT * FROM "stage.B"

• UNION — lowered. One read per operand, combined client-side by deep equality and de-duplicated. Joins, subqueries & sets.
• UNION ALL — lowered. As UNION, but keeps duplicates (plain concatenation).
• INTERSECT — lowered. Keeps rows present in every operand.
• EXCEPT — lowered. Keeps the first operand's rows that appear in none of the rest.

A trailing ORDER BY [LIMIT] after the last operand sorts and cuts the combined set. Operators mix only through parentheses — (A UNION B) EXCEPT C runs; un-parenthesized mixing refuses, by design. Joins, subqueries & sets.

Condition functions

WHERE begins_with(sk, 'EVT#')     -- prefix match (narrows a sort key)WHERE contains(Tags, 'urgent')    -- substring / set membershipWHERE attribute_type(Meta, 'M')   -- DynamoDB type codeWHERE size(Items) > 3             -- length of string / set / list / map

• begins_with(path, prefix) — native. On a queried sort key it narrows the key condition; elsewhere it filters.
• contains(path, value) — native (filter). Substring match on strings, membership in a set or list.
• attribute_type(path, type) — native (filter). Matches DynamoDB type codes (S, N, M, L, …).
• size(path) — native (filter). Length of a string, set, list, or map.

Scalar functions (computed columns)

SELECT upper(Name) AS shout,       coalesce(nickname, Name) AS display,       year(CreatedAt) AS cohort,       from_epoch(ExpiresAt) AS expires_iso,       CASE Status WHEN 1 THEN 'Pending' ELSE 'Other' END AS statusFROM "stage.Users" WHERE pk = 'User#9'

These evaluate per fetched row, after the read — in SELECT lists, SELECT VALUE, and HAVING (distinct from the native WHERE functions above, which DynamoDB runs server-side). A MISSING argument yields MISSING, a NULL yields NULL, a wrongly typed argument yields MISSING — except coalesce / ifnull, which skip both; an unknown function or wrong argument count refuses before the read.

• String — lowered. upper · lower · trim / ltrim / rtrim · length · substring(s, start[, len]) (1-based).
• Numeric — lowered. abs · ceil · floor · mod(a, b).
• Null-handling — lowered. coalesce(…) · ifnull(a, b) — return the first present value.
• Date / time — lowered. year · month · day · hour · minute · second · to_epoch / from_epoch · date_add(unit, n, iso) · date_diff(unit, a, b). ISO-8601 in UTC at second precision, so they round-trip with the clock functions.
• Regex — lowered. regex_like(s, pattern) · regex_replace(s, pattern, repl) · regex_extract(s, pattern[, group]). .NET syntax, timeout-bounded; a malformed literal pattern refuses before the read.
• CASE … END — lowered. Maps a stored value to a label as a SELECT-list expression — simple (CASE attr WHEN v THEN … ELSE … END) and searched (CASE WHEN cond THEN … END). An unmatched row with no ELSE is MISSING-and-omitted; CASE in a normal read WHERE is refused. First queries.

Aggregate functions

SELECT COUNT(*) AS n, AVG(Points) AS avg,       SUM(Points) AS total, MIN(Age) AS lo, MAX(Age) AS hiFROM "stage.Points"WHERE pk = 'User#9'

• COUNT(*) — lowered. Counts matched items regardless of attribute presence.
• COUNT(attr) — lowered. Counts items where attr is present and non-null.
• SUM / AVG — lowered. Numeric fold; non-numeric values are skipped with a disclosure, and an empty fold is NULL, not 0.
• MIN / MAX — lowered. Type-ordered comparison — booleans before numbers before text.
• COUNT / SUM / AVG(DISTINCT attr) — lowered. Fold over distinct values (deep equality); DISTINCT is refused on MIN / MAX.
• GROUP BY / HAVING — lowered. GROUP BY folds one row per distinct key (attribute or document path); HAVING filters the folded rows. Aggregates & profiling.

All aggregates fold the matched read client-side into one row (or one row per group with GROUP BY), reading and billing every matched item. The target may be a document path (SUM(order.total)). Bound the input with WHERE and LIMIT. Full treatment in Aggregates & profiling.

Clock functions

SET CreatedAt = CURRENT_TIMESTAMP        -- ISO-8601 instant, UTCWHERE Day     = CURRENT_DATE             -- ISO-8601 date, UTCSET StampedAt = utcnow()                 -- alias of CURRENT_TIMESTAMPSET ExpiresAt = unix_now()               -- epoch SECONDS (a bare number, for TTL)SET StampedMs = unix_now_ms()            -- epoch MILLISECONDSWHERE Day > CURRENT_DATE - 7             -- arithmetic folds before the wire

• CURRENT_TIMESTAMP / utcnow() — lowered. Substituted as an ISO-8601 instant in UTC at run time; work in reads and writes.
• CURRENT_DATE — lowered. An ISO-8601 date in UTC.
• unix_now() / CURRENT_EPOCH — lowered. Epoch seconds as a bare number — the form TTL attributes store (an ISO string never matches them).
• unix_now_ms() / CURRENT_EPOCH_MS — lowered. Epoch milliseconds, for JS-style timestamps.
• Clock arithmetic — lowered. An integer offset folds into the literal: CURRENT_DATE - 7 (whole days), ± INTERVAL 'n' DAY|HOUR|MINUTE|SECOND, or unix_now() - 3600 (in the rendered base unit). A sub-day unit promotes CURRENT_DATE to a timestamp.

UTC is deliberate: DynamoDB compares ISO strings lexicographically, so a session-local literal silently misses rows. Occurrences inside string literals are untouched. Detail in Writes & transactions.

Kanject extensions

-- key template: re-spells a bare id into the stored key shapeWHERE pk IN (SELECT 'Outbox#{Id}' FROM "stage.Outbox" LIMIT 25)ON c.pk = 'Customer#{CustomerId}'​-- named parameter: a reusable question (lowers to a positional ?)WHERE status = {Status} AND created_at >= {Since:DateTimeOffset}WHERE pk = ?                              -- the lowered positional form

• Key templates — 'Prefix#{Attr}' — Kanject. Re-spell a bare attribute into the stored key shape inside a subquery projection or a join ON condition. Multi-placeholder and transform-aware; the reason bare-id subqueries match nothing. Joins, subqueries & sets.
• Named parameters — {Name} / {Name:format} / {Name|transform} — Kanject. Lift a literal into a reusable, injection-safe placeholder; each lowers to a positional ? with the value bound alongside, never entering the statement text. Authoring is Professional; running a parameterized saved function is free at every edition. Writes & transactions.
• ? positional parameters — Kanject. The lowered form named parameters compile to. Work in single read statements and saved functions; not yet combined with subqueries, aggregates, scripts, or transactions.