Insert

Insert adds one or more values to a table. The values list is parenthesised and comma-separated; a trailing comma is permitted. Values may also come from a nested select query.

Syntax

Railroad

insertintotable(expr-listselect-stmt);

BNF

insert-stmt ::= "insert" "into" identifier "(" expr-list ")" ";"
              | "insert" "into" identifier select-stmt

expr-list ::= expr ( "," expr )*

Rules

  1. Each inserted value must be an object that satisfies the table schema; schema mismatch is a runtime error. (phase: execute)
  2. Duplicate full key replaces the existing row (LMDB put semantics; no NOOVERWRITE). (phase: execute)
  3. Scalar or non-object values in the values list are rejected. (phase: execute)
  4. A trailing comma after the last value in the list is permitted. (phase: parse)

Examples

Minimal

One Object

One object in the values list produces one row.

SQL

create table T;

insert into T ({"x": 1});

select * from T;

Result

[
  { "x": 1 }
]

Empty Values

Empty values list is a no-op.

SQL

create table T;

insert into T ();

select * from T;

Result

[]

Compound

Two Objects

Two objects in one statement produce two rows.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2});

select * from T;

Result

[
  { "x": 1 },
  { "x": 2 }
]

Five Objects

Five objects in one statement are all persisted.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3}, {"x": 4}, {"x": 5});

select * from T;

Result

[
  { "x": 1 },
  { "x": 2 },
  { "x": 3 },
  { "x": 4 },
  { "x": 5 }
]

One Multi-value

One multi-value insert persists all rows.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

select * from T;

Result

[
  { "x": 1 },
  { "x": 2 },
  { "x": 3 }
]

Three Single-value

Three single-value inserts produce the same table as one multi-value insert.

SQL

create table T;

insert into T ({"x": 1});

insert into T ({"x": 2});

insert into T ({"x": 3});

select * from T;

Result

[
  { "x": 1 },
  { "x": 2 },
  { "x": 3 }
]

Values In

Values in one insert may differ in shape.

SQL

create table T;

insert into T ({"x": 1, "y": 2}, {"x": 3});

select * from T;

Result

[
  { "x": 1, "y": 2 },
  { "x": 3 }
]

Values List

Values list may span multiple lines.

SQL

create table T;

insert into T (
    {"x": 1},
    {"x": 2},
    {"x": 3}
);

select * from T;

Result

[
  { "x": 1 },
  { "x": 2 },
  { "x": 3 }
]

Error cases

Object Rejected

A stored row must be an object; a scalar value is rejected.

SQL

create table T;

insert into T (1, 2, 3);

Expected error: schema

Inserting Into

Inserting into an undeclared table is a static error.

SQL

create table T;

insert into Ghost ({"x": 1});

Expected error: static

An Array

An array value as a row is rejected with a schema error (not a panic).

SQL

create table T;

insert into T ([1, 2, 3]);

Expected error: schema

See also