INSERT statement
INSERT
or REPLACE
multiple rows of data into existing columns in a FeatureBase table.
BNF diagrams
column_list
Run SHOW TABLE <tablename>
to learn the structure of a destination table
value_list
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 |