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

Data types and constraints

Data types and constraints are used to define table columns when creating tables via SQL.

expr

Table of contents

Before you begin

Mapping data types

This table provides mapping between FeatureBase SQL data types and internal data types used by the application for configuring ingestion, API calls, etc.

General data type FeatureBase SQL data type Internal data type Additional information
boolean bool bool  
integer int int  
decimal decimal decimal  
not applicable id mutex Table primary key
not applicable * idset
* idsetq		 set Used to reduce table rows and make queries more efficient.
string string keyed mutex  
not applicable * stringset
* stringsetq		 keyed set Used to reduce table rows and make queries more efficient.
timestamp timestamp timestamp  
vector vector vector  

Date/Time data types

The TIMEUNIT data type converts date/time values to integer values representing the time since midnight January 1, 1970, UTC/GMT (the Unix Epoch).

Numeric data types

User data Data type
Boolean, yes/no BOOL
Floating point DECIMAL
Floating point array VECTOR
Unsigned integer ID
Signed integer INT

String data types

Low-cardinality data types

SET and SETQ data types are designed for low-cardinality user data and avoid the need for data normalization:

User data Associated date/time values Data type
Unsigned integer None IDSET
Unsigned integer Yes IDSETQ
String None STRINGSET
String Yes STRINGSETQ

Values are supplied as comma-separated values and once ingested can be queried using SELECT…SETCONTAINS functions:

Constraints

Constraints are applied:

  • to data types to modify and optimize how table data is stored and accessed
  • when a column is created

expr

Constraint Data type
MIN, MAX INT
SCALE DECIMAL
TIMEQUANTUM, TTL IDSETQ
STRINGSETQ
TIMEUNIT TIMESTAMP

Examples

Create Table statement with all 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';

Further information