Data types

The type system is derived from Java types.

Type NameStorage bitsNullableDescription
boolean1NoBoolean true or false.
ipv432Yes0.0.0.1 to 255.255.255. 255
byte8NoSigned integer -128 to 127.
short16NoSigned integer -32768 to 32767.
char16Yesunicode character.
int32YesSigned integer 0x80000000 to 0x7fffffff.
float32YesSingle precision IEEE 754 floating point value.
symbol32YesSymbols are stored as 32-bit signed indexes from symbol table. Each index will have a corresponding string value. Translation from index to string value is done automatically when data is being written or read. Symbol table is stored separately from column.
string32+n*16YesLength-prefixed sequence of UTF-16 encoded characters whose length is stored as signed 32-bit integer with maximum value of 0x7fffffff.
long64YesSigned integer 0x8000000000000000L to 0x7fffffffffffffffL.
date64YesSigned offset in milliseconds from Unix Epoch. While the date data type is available, we highly recommend applying the timestamp data type in its place. The only material advantage of date is a wider time range; timestamp however is adequate in virtually all cases. Date supports fewer functions and uses milliseconds instead of microseconds.
timestamp64YesSigned offset in microseconds from Unix Epoch.
double64YesDouble precision IEEE 754 floating point value.
uuid128YesUUID values. See also the UUID type.
binary64+n*8YesLength-prefixed sequence of bytes whose length is stored as signed 64-bit integer with maximum value of 0x7fffffffffffffffL.
long256256YesUnsigned 256-bit integer. Does not support arbitrary arithmetic operations, but only equality checks. Suitable for storing hash code, such as crypto public addresses.
geohash(<size>)8-64YesGeohash with precision specified as a number followed by b for bits, c for chars. See the geohashes documentation for details on use and storage.

Variable-sized type limitations#

BINARY field size is limited either by 64-Bit signed int (8388608 peta bytes) or disk size, whichever is smaller.

STRING field size is limited by either 32-bit signed int (1073741824 characters) or disk size, whichever is smaller.

Type nullability#

Nullable types use a specific value to mark NULL values:

Type NameNull valueDescription
floatNaNAs defined by IEEE 754 (java.lang.Float.NaN).
doubleNaNAs defined by IEEE 754 (java.lang.Double.NaN).
long2560x8000000000000000800000000000000080000000000000008000000000000000The value equals four consecutive long null literals.
long0x8000000000000000LMinimum possible value a long can take -2^63.
date0x8000000000000000LMinimum possible value a long can take -2^63.
timestamp0x8000000000000000LMinimum possible value a long can take -2^63.
int0x80000000Minimum possible value an int can take, -2^31.
uuid80000000-0000-0000-8000-000000000000Both 64 highest bits and 64 lowest bits set to -2^63.
char0x00000.
geohash(byte)0xffGeohashes from 1 up to included 7 bits.
geohash(short)0xffffGeohashes from 8 up to included 15 bits.
geohash(int)0xffffffffGeohashes from 16 up to included 31 bits.
geohash(long)0xffffffffffffffffGeohashes from 32 up to included 60 bits.
symbol0x80000000Symbols are stored as int offsets in a lookup file.
string0xffffffffStrings are length prefixed, the length is an int and -1 marks it NULL (no further storage is used).
binary0xffffffffffffffffBinary columns are also length prefixed, the length is a long and -1 marks it NULL (no further storage is used).
ipv4nullIPv4 addresses are stored as int.

To filter columns that contain, or don't contain, NULL values use a filter like:

SELECT * FROM <table> WHERE <column> = NULL;
SELECT * FROM <table> WHERE <column> != NULL;

Alternatively, from version 6.3 use the NULL equality operator aliases:

SELECT * FROM <table> WHERE <column> IS NULL;
SELECT * FROM <table> WHERE <column> IS NOT NULL;
note

NULL values still occupy disk space.

The UUID type#

QuestDB natively supports the UUID type, which should be used for UUID columns instead of storing UUIDs as strings. UUID columns are internally stored as 128-bit integers, allowing more efficient performance particularly in filtering and sorting. Strings inserted into a UUID column is permitted but the data will be converted to the UUID type.

Inserting strings into a UUID column
CREATE TABLE my_table (
id UUID
);
[...]
INSERT INTO my_table VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11');
[...]
SELECT * FROM my_table WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

If you use the PostgreSQL Wire Protocol then you can use the uuid type in your queries. The JDBC API does not distinguish the UUID type, but the Postgres JDBC driver supports it in prepared statements:

UUID uuid = UUID.randomUUID();
PreparedStatement ps = connection.prepareStatement("INSERT INTO my_table VALUES (?)");
ps.setObject(1, uuid);

QuestDB Client Libraries can send UUIDs as strings to be converted to UUIDs by the server.

IPv4#

QuestDB supports the IPv4 data type.

The data type adds validity checks and type-specific functions.

They are - as one would imagine - very useful when dealing with IP addresses.

IPv4 addresses exist within the range of 0.0.0.1 - 255.255.255.255.

A full-zero address - 0.0.0.0 is interpreted as null.

Columns may be created with the IPv4 data type like so:

-- Creating a table named traffic with two ipv4 columns: src and dst.
CREATE TABLE traffic (ts timestamp, src ipv4, dst ipv4) timestamp(ts) PARTITION BY DAY;

IPv4 addresses also support a wide range of existing SQL functions and contain their own operators. For a full list, see IPv4 Operators.

Limitations#

IPv4 column types cannot be created via InfluxDB Line Protocol as the protocol lacks support for IPv4. As a result, the server cannot distinguish between string and IPv4 data. However, InfluxDB Line Protocol can still insert string data into a pre-existing column of type IPv4.


โญ Something missing? Page not helpful? Please suggest an edit on GitHub.