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

INSERT statement

INSERT or REPLACE multiple rows of data into existing columns in a FeatureBase table.

BNF diagrams

expr

column_list

expr

Run SHOW TABLE <tablename> to learn the structure of a destination table

value_list

expr

Syntax 

INSERT INTO
  table_name [(column_list)]
  VALUES
      {(value_list),...};

Arguments

Argument Description Required? Additional information
table_name Target table name Yes  
column_list List of columns which must include the _id column Optional FeatureBase assumes values to be inserted into existing columns if omitted
value_list The list of constants and/or functions joined by operators, or a subquery to be inserted into the column. Yes Value list additional

Additional information

Limitations

The INSERT statement has the following limitations:

Limitation Example Result  
Number of values to INSERT must match the number of columns in column_list INSERT INTO productnames (_id, products, sales) VALUES (1, ‘FeatureBase’) Run fails with error  
Values in rows with duplicate _id keys are overwritten INSERT INTO productnames (_id, products, sales) VALUES (1, 'FeatureBase', 2468121), (1, 'Pilosa', 132940); Second row overwrites the first  
Null values in rows with duplicate _id keys are ignored   INSERT INTO competitors (_id, competitor) VALUES (1, 'BitQuick'), (1, NULL) NULL ignored

Value assignment

There are special assignments for certain literal values.

Literal Value Target Data Type Result Additional information
,'', string '' (empty string)  
,NULL,(case insensitive) All unless explicitly listed NULL  
[] idset
stringset		 [] (empty set) Stores an empty set for new records and existing NULL records. Keeps existing values in set otherwise
{} idsetq
stringsetq		 Curly brackets surround each datestamp and value to be inserted into the array May also be used for vector arrays

Valid Timequantum constraint values

IDSETQ() and STRINGSETQ() Timequantum constraints store integer value UNIX-epoch timestamps which are associated with specific values.

UPDATE/REPLACE behavior

FeatureBase ingest uses UPSERT behavior, but the ways a value updates varies depending on the data type.

Literal Value Target Data Type Resultant Additional information
Value of same type All unless explicitly listed Replace existing value with literal value  
string literal stringset Add new value to existing set of values if it does not exist  
string literal stringsetq Add new value to existing set of values if it does not exist or update associated timestamp and views if value does exist  
integer literal idset Add new value to existing set of values if it does not exist  
integer literal idsetq Add new value to existing set of values if it does not exist or update associated timestamp and views if value does exist  
,NULL, All unless explicitly listed set to NULL  
,[], stringset
idset
stringsetq
idsetq		 Keep existing set of values or set to [] (empty set) if existing set is NULL  

Examples

INSERT examples

Statement Target table
INSERT INTO cosvec-target cosvec-target
INSERT INTO tan-target tan-target

Further information