SQL reference
Data types
The column types of NYXDB, their spellings, bounds and index capabilities, and the NULL rule.
The table below lists the column types and the spellings CREATE TABLE accepts.
Type names are case-insensitive.
Column types
| Spelling | Values | Bounds | Index structures |
|---|---|---|---|
BOOL, BOOLEAN | TRUE, FALSE | BLOOM, POSTINGS | |
INT8, INT16, INT32, INT64 | signed integers of 8 to 64 bits | the type's range | MINMAX, BLOOM, POSTINGS |
INT128, INT256 | signed integers of 128 and 256 bits | the type's range | MINMAX, BLOOM, POSTINGS |
UINT8, UINT16, UINT32, UINT64 | unsigned integers of 8 to 64 bits | the type's range | MINMAX, BLOOM, POSTINGS |
UINT128, UINT256 | unsigned integers of 128 and 256 bits | the type's range | MINMAX, BLOOM, POSTINGS |
FLOAT32, FLOAT64 | IEEE binary floating point | MINMAX | |
DECIMAL(p, s) | exact decimal with precision p and scale s | 1 to 76 digits of precision; s at most p | MINMAX, BLOOM, POSTINGS |
STRING | UTF-8 text, checked on write | at most 16 MiB per value | MINMAX, BLOOM, POSTINGS |
BYTES | a byte string | at most 16 MiB per value | MINMAX, BLOOM, POSTINGS |
FIXEDSTRING(n) | a byte string of fixed length n | n from 1 to 1,048,576 | MINMAX, BLOOM, POSTINGS |
DATE | a calendar date | years 0001 to 9999 | MINMAX, BLOOM, POSTINGS |
DATETIME64(p), DATETIME64(p, 'tz') | a UTC instant with p fractional-second digits | p from 0 to 9; the time-zone text at most 64 bytes; years 0001 to 9999 | MINMAX, BLOOM, POSTINGS |
UUID | a 128-bit identifier | MINMAX, BLOOM, POSTINGS |
Parameterized types are checked against their bounds when the statement is
parsed. VALID BY and REVISION BY name integer or timestamp columns. ENCODING LOW_CARDINALITY is dictionary encoding and is intended for
STRING columns with few distinct values.
Writing values
Values are written as SQL literals. BOOL takes TRUE or FALSE; numeric
types take a number; DECIMAL takes a number or a string. STRING, BYTES,
FIXEDSTRING, DATE ('YYYY-MM-DD'), DATETIME64
('YYYY-MM-DDTHH:MM:SS[.fraction]Z') and UUID take a string. The full list is
on INSERT.
NULL
- A column is
NOT NULLunlessNULLis written after its type inCREATE TABLEorADD COLUMN. - A write that omits a column uses its
DEFAULT, or NULL when the column is nullable. - Primary-key columns and the columns named by
VALID BYandREVISION BYare never null. - A comparison with a NULL operand is NULL,
AND,ORandNOTfollow SQL three-valued logic, and aWHEREkeeps only rows whose condition is true. Test for nulls withIS NULLandIS NOT NULL.
Composite and network types
| Spelling | Values |
|---|---|
ARRAY(T) | an ordered list of values of type T |
TUPLE(T1, T2, …) | a fixed sequence of typed fields |
MAP(K, V) | key–value pairs |
JSON | a JSON document |
IPV4, IPV6 | network addresses |
ENUM | one of a declared set of names |
Nullability is the NULL column option, and dictionary encoding is
ENCODING LOW_CARDINALITY.
Lexical syntax
Keywords, identifiers, literals, comments, parameters, INTERVAL literals and the one-statement, 64 KiB request boundary.
CREATE TABLE
The full CREATE TABLE grammar - columns, nullability, defaults, codecs, reducers, keys, groups, indexes, mode, ordering, shards, partitions and storage profile.