Writing data

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 }
]

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 }
]

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

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 }
]

Inserting Into

Inserting into an undeclared table is a static error.

SQL

create table T;

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

Expected error: static

Empty Values

Empty values list is a no-op.

SQL

create table T;

insert into T ();

select * from T;

Result

[]

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

Empties Table

Delete with no where removes every row.

SQL

create table T;

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

delete from T;

select * from T;

Result

[]

Matching Rows

Delete with a predicate removes only matching rows.

SQL

create table T;

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

delete from T where T.x > 1;

select * from T;

Result

[
  { "x": 1 }
]

An Explicit

An explicit as alias binds the predicate's references.

SQL

create table T;

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

delete from T as r where r.x = 2;

select * from T;

Result

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

Table Unchanged

A predicate that matches nothing leaves the table unchanged.

SQL

create table T;

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

delete from T where T.x > 100;

select * from T;

Result

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

Deleting From

Deleting from an empty table succeeds and yields nothing.

SQL

create table T;

delete from T;

select * from T;

Result

[]

Reuses Table

A table is reusable after a delete-all.

SQL

create table T;

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

delete from T;

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

select * from T;

Result

[
  { "x": 9 }
]

Deleting From

Deleting from an undeclared table is a static error.

SQL

create table T;

delete from Ghost;

Expected error: static

Table Place

Clear removes every row but leaves the table in place.

SQL

create table T;

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

clear table T;

select * from T;

Result

[]

New Rows

A cleared table is still resolvable and accepts new rows.

SQL

create table T;

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

clear table T;

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

select * from T;

Result

[
  { "x": 9 }
]

Clearing An

Clearing an undeclared table is a static error.

SQL

clear table Ghost;

Expected error: static

After Drop,

After drop, the table can no longer be selected from.

SQL

create table T;

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

drop table T;

select * from T;

Expected error: static

Is Empty

A table re-created after drop is fresh and empty.

SQL

create table T;

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

drop table T;

create table T;

select * from T;

Result

[]

Dropping An

Dropping an undeclared table is a static error.

SQL

drop table Ghost;

Expected error: static