INSERT, UPSERT and REPLACE

Write one row per statement with INSERT, UPSERT or REPLACE, optionally into one attribute group, and how literals map to column types.

Three statements write a row from a list of values. They share one form:

INSERT | UPSERT | REPLACE INTO <table> (<column>, …) VALUES (<value>, …) [ATTRIBUTE GROUP <group>]

The column list has as many entries as the value list. Each value is a literal (a number, a single-quoted string, TRUE, FALSE or NULL), an expression, or the keyword DEFAULT. VALUES (…), (…) writes several rows in one statement. Bulk loads also use the write-batch and bulk-load routes described on Protocol.

What each statement does

Statement	Existing key	New key	Allowed on
`INSERT`	reports "insert target already exists"	creates the row	every mode; the write of an `APPEND` table
`UPSERT`	changes the named columns, the others keep their values	creates the row	`UPSERT`, `VERSIONED`
`REPLACE`	writes the complete row	creates the row	`UPSERT`, `VERSIONED`

A column the statement does not name takes its DEFAULT on a new row, or NULL when it is nullable. The primary-key columns and the SHARD BY columns are always named, with non-null values. A group-scoped write names the key columns and columns of its group.

INSERT

INSERT INTO transfers (
  chain_id, token, sender, recipient, amount, transferred_at, _index
)
VALUES (
  1, 'USDC', '0x4b1e7c2a', '0x9c2e4f1a', 2500000000, '2026-10-01 12:00:00.000',
  1001
);

UPSERT

UPSERT INTO balances (
  chain_id, address, token, amount, updated_at, balance_index, balance_version
)
VALUES (
  1, '0x9c2e4f1a', 'USDC', 2500000000, '2026-10-01 12:00:00.000', 1001, 1
);

On an existing key, UPSERT changes the named columns and leaves the others as they were.

REPLACE

REPLACE writes every column of the row. On a table with attribute groups, a REPLACE supersedes the groups written before it:

REPLACE INTO balances (
  chain_id, address, token, amount, updated_at, balance_index, balance_version,
  transfer_count, last_transfer_at, activity_index
)
VALUES (
  1, '0x9c2e4f1a', 'USDC', 0, '2026-10-02 00:00:00.000', 2001, 1, 0, NULL, 2001
);

Writing one attribute group

With a trailing ATTRIBUTE GROUP <group>, the statement names the key columns and the columns of that group:

UPSERT INTO balances (
  chain_id, address, token, amount, updated_at, balance_index, balance_version
)
VALUES (1, '0x9c2e4f1a', 'USDC', 2750000000, '2026-10-01 12:05:00.000', 1002, 1)
ATTRIBUTE GROUP holding;

See Attribute groups for how group writes combine.

Literals and column types

A value is checked against its column's type when the statement is bound:

Column type	Literal
`BOOL`	`TRUE` or `FALSE`
integer types, `FLOAT32`, `FLOAT64`	a number, optionally signed; it must fit the type
`DECIMAL(p, s)`	a number or a string, within the precision and scale
`STRING`	a string; it must be valid UTF-8
`BYTES`, `FIXEDSTRING(n)`	a string, taken as bytes
`DATE`	a string `'YYYY-MM-DD'`
`DATETIME64(p)`	a string `'YYYY-MM-DDTHH:MM:SS[.fraction]Z'`, with at most `p` fraction digits
`UUID`	a string in the canonical 8-4-4-4-12 form
any nullable column	`NULL`

A string escapes a single quote by doubling it. See Data types.

Writes to transformation targets

A transformation owns what it writes: a whole table, or one attribute group of it. Producers write the other groups of the same rows.

UPDATE and DELETE
Transactions for what one statement guarantees
Tables for the modes

INSERT, UPSERT and REPLACE

On this page