Create Table

Create Table declares a table in the catalog. An optional schema lists key columns (in declaration order) that form the composite physical key. Key columns must be int or string. Keyless tables accept any object and preserve insertion order via surrogate ids.

Syntax

Railroad

createtablename()key-column,key-column);

BNF

create-table-stmt ::= "create" "table" identifier [ "(" [ key-column ( "," key-column )* ] ")" ] ";"

key-column ::= identifier ( "int" | "string" )

Rules

  1. Without a schema, the table accepts any JSON object. (phase: catalog)
  2. Declared columns form the composite key; key columns must be int or string. (phase: catalog)
  3. Fields are non-null by default; declare T | null to permit null (general unions are not supported). (phase: catalog)
  4. Creating a table that already exists is a static error (IF NOT EXISTS is not supported). (phase: catalog)
  5. Inserts that violate the declared schema (missing keys, wrong types, extra keys on closed schemas) error at runtime. (phase: execute on insert)

Examples

Minimal

Whole Objects

A keyless table stores and returns whole objects.

SQL

create table t;

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

select * from t;

Result

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

Compound

Ones X

A keyless table accepts any object, including ones with no x.

SQL

create table t;

insert into t ({"y": 2, "z": 3});

select * from t;

Result

[
  { "y": 2, "z": 3 }
]

Surrogate Ids

Surrogate ids increment, so rows come back in insertion order.

SQL

create table t;

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

select * from t;

Result

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

Int Key

Int key with payload round-trips whole object.

SQL

create table t (x int);

insert into t ({"x": 1, "z": 9});

select * from t;

Result

[
  { "x": 1, "z": 9 }
]

Rows Inserted

Rows inserted out of order come back sorted by the int key.

SQL

create table t (x int);

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

select * from t;

Result

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

Negative Ints

Negative ints sort before zero and positives (sign-flip encoding).

SQL

create table t (x int);

insert into t ({"x": 1}, {"x": -5}, {"x": 0}, {"x": -1});

select * from t;

Result

[
  { "x": -5 },
  { "x": -1 },
  { "x": 0 },
  { "x": 1 }
]

Re-inserting The

Re-inserting the same key overwrites (last write wins).

SQL

create table t (x int);

insert into t ({"x": 1, "v": 100});

insert into t ({"x": 1, "v": 200});

select * from t;

Result

[
  { "x": 1, "v": 200 }
]

Int Key

A float subscript coerces like cast( as int) to find the int key.

SQL

create table t (x int);

insert into t ({"x": 1, "z": 9});

select t[1.5];

Result

[
  { "x": 1, "z": 9 }
]

String Key

String key with payload round-trips whole object.

SQL

create table t (x string);

insert into t ({"x": "a", "z": 9});

select * from t;

Result

[
  { "x": "a", "z": 9 }
]

Rows Come

Rows come back in lexicographic key order.

SQL

create table t (x string);

insert into t ({"x": "c"}, {"x": "a"}, {"x": "b"});

select * from t;

Result

[
  { "x": "a" },
  { "x": "b" },
  { "x": "c" }
]

Composite (int,

Composite (int, string) key round-trips whole object.

SQL

create table t (a int, b string);

insert into t ({"a": 1, "b": "x", "z": 9});

select * from t;

Result

[
  { "a": 1, "b": "x", "z": 9 }
]

Sort By

Sort by first component, tie-break on the second.

SQL

create table t (a int, b string);

insert into t ({"a": 2, "b": "a"}, {"a": 1, "b": "y"}, {"a": 1, "b": "x"});

select * from t;

Result

[
  { "a": 1, "b": "x" },
  { "a": 1, "b": "y" },
  { "a": 2, "b": "a" }
]

Composite (string,

Composite (string, int) key round-trips whole object.

SQL

create table t (a string, b int);

insert into t ({"a": "x", "b": 1, "z": 9});

select * from t;

Result

[
  { "a": "x", "b": 1, "z": 9 }
]

Sort By

Sort by string first, tie-break on the int.

SQL

create table t (a string, b int);

insert into t ({"a": "b", "b": 1}, {"a": "a", "b": 2}, {"a": "a", "b": 1});

select * from t;

Result

[
  { "a": "a", "b": 1 },
  { "a": "a", "b": 2 },
  { "a": "b", "b": 1 }
]

Composite (int,

Composite (int, int) key round-trips whole object.

SQL

create table t (a int, b int);

insert into t ({"a": 1, "b": 2, "z": 9});

select * from t;

Result

[
  { "a": 1, "b": 2, "z": 9 }
]

Sort By

Sort by first int, tie-break on the second int.

SQL

create table t (a int, b int);

insert into t ({"a": 2, "b": 1}, {"a": 1, "b": 2}, {"a": 1, "b": 1});

select * from t;

Result

[
  { "a": 1, "b": 1 },
  { "a": 1, "b": 2 },
  { "a": 2, "b": 1 }
]

Composite (string,

Composite (string, string) key round-trips whole object.

SQL

create table t (a string, b string);

insert into t ({"a": "x", "b": "y", "z": 9});

select * from t;

Result

[
  { "a": "x", "b": "y", "z": 9 }
]

Sort By

Sort by first string, tie-break on the second.

SQL

create table t (a string, b string);

insert into t ({"a": "b", "b": "a"}, {"a": "a", "b": "b"}, {"a": "a", "b": "a"});

select * from t;

Result

[
  { "a": "a", "b": "a" },
  { "a": "a", "b": "b" },
  { "a": "b", "b": "a" }
]

Before Ab

A shorter first component sorts before a longer one that shares its prefix, regardless of the second component — proves the string terminator. ("a","z") must sort before ("ab","a").

SQL

create table t (a string, b string);

insert into t ({"a": "ab", "b": "a"}, {"a": "a", "b": "z"});

select * from t;

Result

[
  { "a": "a", "b": "z" },
  { "a": "ab", "b": "a" }
]

Error cases

Inserting Without

Inserting without the key field is a schema error.

SQL

create table t (x int);

insert into t ({"z": 9});

Expected error: schema

Wrong Type

A string where an int key is declared is a schema error.

SQL

create table t (x int);

insert into t ({"x": "a"});

Expected error: schema

Inserting Without

Inserting without the key field is a schema error.

SQL

create table t (x string);

insert into t ({"z": 9});

Expected error: schema

Wrong Type

A number where a string key is declared is a schema error.

SQL

create table t (x string);

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

Expected error: schema

Missing The

Missing the first key field is a schema error.

SQL

create table t (a int, b string);

insert into t ({"b": "x"});

Expected error: schema

Missing The

Missing the second key field is a schema error.

SQL

create table t (a int, b string);

insert into t ({"a": 1});

Expected error: schema

Wrong Type

Wrong type for the first key field is a schema error.

SQL

create table t (a int, b string);

insert into t ({"a": "q", "b": "x"});

Expected error: schema

Wrong Type

Wrong type for the second key field is a schema error.

SQL

create table t (a int, b string);

insert into t ({"a": 1, "b": 2});

Expected error: schema

Missing The

Missing the int component is a schema error.

SQL

create table t (a string, b int);

insert into t ({"a": "x"});

Expected error: schema

Type Second

A string where the int component is declared is a schema error.

SQL

create table t (a string, b int);

insert into t ({"a": "x", "b": "y"});

Expected error: schema

Missing A

Missing a key component is a schema error.

SQL

create table t (a int, b int);

insert into t ({"a": 1});

Expected error: schema

Missing A

Missing a key component is a schema error.

SQL

create table t (a string, b string);

insert into t ({"a": "x"});

Expected error: schema

Float Key

A float key column is rejected at create.

SQL

create table t (x float);

Expected error: static

Bool Key

A bool key column is rejected at create.

SQL

create table t (x bool);

Expected error: static

See also