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 for INSERT and BULK INSERT statements

General data type FeatureBase SQL data type Internal data type
boolean BOOL bool
integer INT int
decimal DECIMAL decimal
unsigned integer ID mutex
low-cardinality unsigned integer * IDSET and IDSETQ set
string STRING keyed mutex
low-cardinality string * STRINGSET and STRINGSETQ keyed set
timestamp TIMESTAMP timestamp
varchar VARCHAR  
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
Unsigned integer ID
Comma-separated unsigned integers SET and SETQ
Signed integer INT
Floating point array VECTOR

String data types

User data Data type
Sequence of characters up to 256mb STRING
Comma-separated array of string values SET and SETQ
Fixed-length sequence of characters VARCHAR

Low-cardinality 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

  • SET and SETQ

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