Skip to main content Link Menu Expand (external link) Document Search Copy Copied

CREATE TABLE statement

Creates a FeatureBase table. The table already exists and IF NOT EXISTS is not specified the statement will not be successful.

BNF diagrams

expr

COLUMN_LIST

expr

Type_name

expr

Column_constraint

expr

Table_options

expr

Syntax

CREATE TABLE
  [IF NOT EXISTS]
  table_name
  (COLUMN_LIST)
  [with comment 'comment'];

Arguments

Argument Description Required? Further information
table_name Valid table name Yes Naming standards
IF NOT EXISTS Optional argument that stops statement execution if a table of the same name already exists No  
COLUMN_LIST List of column names, data types and optional constraints. The list must include the _id column Yes * _id column
* Naming standards
* Data types
with comment Optional string literal that describes the table No  

Additional information

Naming standards

FeatureBase identifiers (including object names such as databases, tables and columns) start with a lower-case alphabetic character and can include:

  • lower-case alphabetic characters
  • numbers 0-9
  • dash - and underscore _ characters.

  • table names can be up to 230 characters in length

  • column names can be up to 64 characters in length

_id column

The table ID column (_id) is used as the table primary key and must uniquely represent a record in your table.

UI Type SQL data type Data source table unique key
Number id Positive integer
String string String key or a combination of columns used to create a unique key

Examples

create table products
  (_id id, item string, price decimal(2) stock int);
create table services
  (_id id, servicelist string, price decimal(2));

CREATE TABLE with integer constraints

create table doctest
  (
    _id id,
    numbers int min -991 max 991,
    words string
  );

CREATE TABLE with STRINGSET data type

CREATE TABLE myspecies
  (
    _id string,
    species stringset
  );
create table segments  
    (_id id, segment stringset);  

insert into segments(_id, segment)  
    values (1, ['RED', 'BLUE', 'GREEN']),
      (2, ['GREEN']),
      (3, ['RED', 'BLUE', 'GREEN']);
CREATE TABLE segments (
    _id id,
    segment stringsetq timequantum 'YMDH'
);

CREATE TABLE with TIMEQUANTUM constraints

CREATE TABLE timeq (
_id id,
stringsetcolq stringsetq timequantum 'YMD',
idsetcolq idsetq timequantum 'YMD'
);

CREATE TABLE with vector array

CREATE TABLE products (_id id, description string, dabed vector(768));

CREATE TABLE with all column types

create table allcoltypes
  (
    _id id,
    intcol int min 0 max 10000,
    boolcol bool,
    timestampcol timestamp timeunit 'ms',
    decimalcol decimal(2),
    stringcol string,
    stringsetcol stringset,
    stringsetcolq stringsetq timequantum 'YMD' ttl '24h',
    idcol id,
    idsetcol idset,
    idsetcolq idsetq timequantum 'YMD' ttl '24h',
    vectorcol vector(768)
  )
  with comment 'table containing all column types';

Further information