# 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](/docs/drivers/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

```sql
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

```sql
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:

```sql
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:

```sql
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](/docs/concepts/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](/docs/sql/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.

## Related pages

- [UPDATE and DELETE](/docs/sql/update-delete)
- [Transactions](/docs/sql/transactions) for what one statement guarantees
- [Tables](/docs/concepts/tables) for the modes

---

Source: https://nyxdb.io/docs/sql/insert/