# PartiQL: keyword reference

The flat index for the [dialect series](https://www.kanject.com/docs/dynostudio-partiql/): 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](https://www.kanject.com/docs/dynostudio-partiql/): **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:

- **[SELECT](https://www.kanject.com/docs/dynostudio-select/)** — Point reads, GSI lookups, computed columns, counts — and the cost rule.
- **[INSERT & upserts](https://www.kanject.com/docs/dynostudio-insert/)** — Object-literal writes, ON CONFLICT skip / replace / upsert, TTL stamps.
- **[UPDATE](https://www.kanject.com/docs/dynostudio-update/)** — Single-item writes, RETURNING, counters, preview-first sweeps.
- **[DELETE](https://www.kanject.com/docs/dynostudio-delete/)** — Keyed deletes, keep-a-copy RETURNING, stale-row sweeps — and when TTL beats a sweep.
- **[PROFILE TABLE](https://www.kanject.com/docs/dynostudio-profile-table/)** — Discover an unfamiliar table's shape before you query it.
- **[Transactions](https://www.kanject.com/docs/dynostudio-transactions/)** — All-or-nothing write blocks, condition guards, snapshot reads.
- **[CREATE & DROP TABLE](https://www.kanject.com/docs/dynostudio-create-table/)** — Key types, billing modes, and the type-the-name drop confirmation.
- **[CREATE GSI](https://www.kanject.com/docs/dynostudio-create-gsi/)** — Single- and multi-key indexes, projections, free auto-backfill.
- **[ANALYTICS REPLICA](https://www.kanject.com/docs/dynostudio-analytics-replica/)** — Provision, retune and tear down a DynamoDB → S3 Tables replica.
- **[Marker indexes](https://www.kanject.com/docs/dynostudio-marker-indexes/)** — UNIQUE / COLLECTION / RANGE constraints, backfilled and reconciled.

## Statements

```sql
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](https://www.kanject.com/docs/dynostudio-partiql-basics/) and [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-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](https://www.kanject.com/docs/dynostudio-partiql-basics/).
- **`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](https://www.kanject.com/docs/dynostudio-partiql-writes/).
- **`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](https://www.kanject.com/docs/dynostudio-partiql-writes/).
- **`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](https://www.kanject.com/docs/dynostudio-partiql-writes/).
- **`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](https://www.kanject.com/docs/dynostudio-partiql-writes/).
- **`PROFILE TABLE`** — *lowered.* Samples a table and reports per-attribute coverage and type distribution. [Aggregates & profiling](https://www.kanject.com/docs/dynostudio-partiql-aggregates/).
- **`BEGIN TRANSACTION` / `COMMIT`** — *native.* Wraps a write script into one all-or-nothing transaction. [Writes & transactions](https://www.kanject.com/docs/dynostudio-partiql-writes/).

## Schema & DDL

```sql
CREATE TABLE "stage.Ledger" (PARTITION KEY pk STRING, SORT KEY sk STRING) WITH BILLING = ON_DEMAND
DROP   TABLE "stage.Ledger"                          -- type-the-name confirmation
CREATE GSI "byRegionStatus" ON "stage.Orders"        -- up to 4 partition + 4 sort keys
  PARTITION KEY (region, tenant) SORT KEY (status, createdAt) PROJECT ALL
CREATE ANALYTICS REPLICA FOR TABLE "stage.Orders"    -- S3 Tables zero-ETL integration
CREATE UNIQUE     INDEX "uxTenantEmail" ON "stage.Users" (tenantId, email)  -- Kanject marker
CREATE UNIQUE     INDEX ON "stage.Users" (email) WHERE status = 'active'    -- partial marker
CREATE COLLECTION INDEX ON "stage.Orders" (status)   -- membership marker (one row per item)
CREATE RANGE      INDEX ON "stage.Scores" (points)   -- range-scannable marker
ALTER  UNIQUE     INDEX ON "stage.Users" (email) REFRESH   -- reconcile reserved rows
DROP   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](https://www.kanject.com/docs/dynostudio-partiql-ddl/#s3-tables-analytics-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

```sql
FROM "stage.Users"."ByOrg"          -- base table or "table"."index"
WHERE OrgId = 'Org#kanject'            -- filter / key condition
GROUP BY Status                     -- fold matches into groups
ORDER BY Age DESC NULLS LAST        -- sort (ASC | DESC, NULLS FIRST | LAST)
LIMIT 50 OFFSET 10                  -- page window
RETURNING ALL OLD *                 -- echo the written item
JOIN "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](https://www.kanject.com/docs/dynostudio-partiql-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](https://www.kanject.com/docs/dynostudio-partiql-reads/).
- **`GROUP BY`** — *lowered.* One result row per distinct key combination, folded client-side. [Aggregates & profiling](https://www.kanject.com/docs/dynostudio-partiql-aggregates/).
- **`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](https://www.kanject.com/docs/dynostudio-partiql-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](https://www.kanject.com/docs/dynostudio-partiql-joins/).
- **`CROSS JOIN`** *(no `ON`)* — *lowered.* The Cartesian product of both fully-drained sides, capped at 10,000 output rows. [Joins, subqueries & sets](https://www.kanject.com/docs/dynostudio-partiql-joins/).
- **Two or more `JOIN`s** *(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](https://www.kanject.com/docs/dynostudio-partiql-joins/).
- **`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

```sql
WHERE Age >= 18 AND Country <> 'US'          -- comparisons + AND
WHERE Score BETWEEN 10 AND 20                -- range
WHERE pk IN ('User#1', 'User#2')             -- membership
WHERE 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 absence
SELECT DISTINCT Country FROM "stage.Users"   -- de-duplicate
SELECT '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](https://www.kanject.com/docs/dynostudio-partiql-joins/).
- **`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](https://www.kanject.com/docs/dynostudio-partiql-joins/).
- **`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](https://www.kanject.com/docs/dynostudio-partiql-joins/).

## Set operators

```sql
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](https://www.kanject.com/docs/dynostudio-partiql-joins/).
- **`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](https://www.kanject.com/docs/dynostudio-partiql-joins/).

## Condition functions

```sql
WHERE begins_with(sk, 'EVT#')     -- prefix match (narrows a sort key)
WHERE contains(Tags, 'urgent')    -- substring / set membership
WHERE attribute_type(Meta, 'M')   -- DynamoDB type code
WHERE 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)

```sql
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 status
FROM "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](https://www.kanject.com/docs/dynostudio-partiql-basics/).

## Aggregate functions

```sql
SELECT COUNT(*) AS n, AVG(Points) AS avg,
       SUM(Points) AS total, MIN(Age) AS lo, MAX(Age) AS hi
FROM "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](https://www.kanject.com/docs/dynostudio-partiql-aggregates/).

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](https://www.kanject.com/docs/dynostudio-partiql-aggregates/).

## Clock functions

```sql
SET CreatedAt = CURRENT_TIMESTAMP        -- ISO-8601 instant, UTC
WHERE Day     = CURRENT_DATE             -- ISO-8601 date, UTC
SET StampedAt = utcnow()                 -- alias of CURRENT_TIMESTAMP
SET ExpiresAt = unix_now()               -- epoch SECONDS (a bare number, for TTL)
SET StampedMs = unix_now_ms()            -- epoch MILLISECONDS
WHERE 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](https://www.kanject.com/docs/dynostudio-partiql-writes/).

## Kanject extensions

```sql
-- key template: re-spells a bare id into the stored key shape
WHERE 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](https://www.kanject.com/docs/dynostudio-partiql-joins/).
- **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](https://www.kanject.com/docs/dynostudio-partiql-writes/).
- **`?` 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.

> **Lookup, not lesson:** This page is a lookup, not a lesson. If a keyword's behaviour surprises you, the page link beside it walks through *why*. New here? Start at [First queries](https://www.kanject.com/docs/dynostudio-partiql-basics/), or pick your entry point from [the series overview](https://www.kanject.com/docs/dynostudio-partiql/).

---
_Source: https://www.kanject.com/docs/dynostudio-partiql-keywords/ · Kanject Docs_