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

SET and SETQ data types

In a typical database, each field – the intersection of row and column – contains an individual value.

FeatureBase SET and SETQ data types are designed to contain an array of comma-separated values within each field.

This means:

  • data does not need to be normalized into separate tables,
  • import/ingest actions are faster because there are less rows to import,
  • queries are faster


{ID | STRING}SET{Q TIMEQUANTUM '<date-unit>' [TTL '<int-value><time-unit>']}


Argument Description Required? Default Additional information
ID Values to be inserted conform to ID data type     ID data type
STRING Values to be inserted conform to STRING data type     STRING data type
SET Comma-separated array of values Yes    
SETQ Comma-separated array of values identified by a Unix-epoch or ISO-8601 timestamp     TIMESTAMP data type
TIMEQUANTUM SETQ constraint that creates views on SETQ data for each <date-unit> Y   TIMEQUANTUM views
* TIMEQUANTUM view deletion
<date-unit> One or more sequential, descending date units, defined as Y, M, D, H Y   TIMEQUANTUM views
TTL Governs automatic deletion of TIMEQUANTUM views Optional 0s (disables TTL) * TTL (Time To Live)
* TIMEQUANTUM view deletion
'<int-value><time-unit>' String literal that requires a positive integer with time-unit Y '0s' TTL (Time To Live)

Additional information

INSERT and UPSERT behaviour

INSERT or UPSERT behaviour occurs when any of the following are true:

Change type _id TIMEQUANTUM SET values
INSERT New New New
UPSERT Existing Existing New


A TIMEQUANTUM view is created for each <date-unit> defined in a CREATE TABLE statement

Name <date-unit> declaration View timestamp
Year Y YYYY-01-01T00:00:00.000Z
Month M YYYY-MM-01T00:00:00.000Z
Day D YYYY-MM-DDT00:00:00.000Z
Hour H YYYY-MM-DDTHH:00:00.000Z


  • improve the responsiveness of Range queries where a query uses the same combination of date units
  • increase storage overheads which may require the use of TTL(time-to-live)

TTL (Time To Live)

An integer and time unit are used to calculate the number of seconds before a TIMEQUANTUM view can be deleted.

TTL defaults to 0s which means:

  • No TIMEQUANTUM views are deleted,
  • Disk requirements will increase

FeatureBase recommends TTL '1h' for best results

Name <time-unit> declaration
Hour h
Minute m
seconds (default) s
milliseconds ms
microseconds us
nanoseconds ns

TIMEQUANTUM view deletion

To determine when TIMEQUANTUM views are deleted, FeatureBase performs the following operations:

  • Timestamp values are converted to Unix-epoch seconds since 1970-01-01
  • TTL is converted to seconds, regardless of its <time-unit>

If the following equation is true, the TIMEQUANTUM view is deleted:

<database-timestamp> - <view-timestamp> >= <ttl-value>

TIMEQUANTUM view deletion may not occur immediately because:

  • the database timestamp is governed by the vendor region
  • views may contain large quantities of data

Value definition

Use the following syntax to INSERT or BULK INSERT values to SET and SETQ columns in a target table:

Column data type Assignment Additional information
SET [<value>,...]  
SETQ {<timestamp>,[<value>,...]}  

Use semicolons to separate values in a CSV data source intended for SET and SETQ columns. e.g., <previous-value>,<value>;...,<next-value>

GROUP BY issues on SET and SETQ data 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';

INSERT statement

INSERT INTO all-datatypes (_id, idsetcol, idsetqcol, stringsetcol, stringsetqcol) VALUES

(1, [10,20,30],
['ten', 'twenty', 'thirty'],

Further information