INSERT

Adds one or more rows to a table. Each row either supplies its primary key or leaves it out and has the engine issue one.

Syntax

INSERT INTO <table> VALUES (<value> [, ...]) [, (<value> [, ...])] ...

There is no column list. Values are positional.

Examples

Let the engine issue the key

On passengers (id, name, member, joined_on), three values per row leave the key out:

INSERT INTO passengers VALUES ('Oh Minseo', 0, '2026-03-01');

INSERTED oid=4005 id=9 page=133 slot=8

Supply the key

Four values per row: the first is the key.

INSERT INTO passengers VALUES (500, 'Shin Areum', 1, '2022-08-15');

INSERTED oid=4005 id=500 page=133 slot=9

Several rows, mixed

One statement can mix supplied and issued keys. An issued key is always above every key supplied before it.

INSERT INTO passengers VALUES (7000, 'Baek Junho', 0, '2026-04-02'), ('Moon Jia', 1, '2023-01-09');   -- Moon Jia gets an id above 7000

INSERTED oid=4005 rows=2 first_id=7000 last_id=7001

Values per row

On a table with n columns, a row has either n values (the first is the key) or n − 1 values (the key is issued). Any other count is refused, naming both:

ERR expected 4 value(s) including primary-key column 'id', or 3 to have it issued; got 5

Values are integers, 'strings', bare numbers, and NULL for columns declared NULL. Expressions are not allowed in VALUES.

Supplied keys

A supplied key must be an integer from 1 to 2⁴⁸ − 1. It is refused, with its byte position and nothing written, for exactly two reasons:

Reason	Error
The key is taken by a live row, or by a deleted row that has not been purged	AlreadyExists: duplicate primary key ...
The key is outside the range	OutOfRange: primary key ... is outside the Keystone id space ...

A key whose insert was rolled back is free again. Keys can be supplied in any order; each row is placed where its key sorts.

How it works

Each row, in order, goes through the full single-row path: foreign-key check, assertion check, key, placement, index and Cabin maintenance, and the write-ahead log. A multi-row insert saves parsing and round trips, not checks.

A multi-row insert is atomic. If any row is refused, the error gives the row number, such as (row 3), and no row is inserted.

Replies

Rows	Reply
One	INSERTED oid=<table> id=<key> page=<page> slot=<slot>
Several	INSERTED oid=<table> rows=<n> first_id=<key> last_id=<key>

first_id and last_id are the first and last rows in statement order, not the lowest and highest keys.

Limitations

• At most max_insert_rows rows per statement (default 1,024). More is refused, not truncated.
• No column list, no INSERT ... SELECT, no DEFAULT.
• No escape for a quote inside a string literal.

See also

• CREATE TABLE: the primary key
• UPDATE
• PURGE