ILP Columnset Value Types

Integer#

64-bit signed integer values, which correspond to QuestDB type long. The values are required to have i suffix. For example:

temps,device=cpu,location=south value=96i 1638202821000000000\n

Sometimes integer values are small and do not warrant 64 bits to store them. To reduce storage for such values it is possible to create a table upfront with smaller type, for example:

create table temps(device symbol, location symbol, value short)

The line above will be accepted and 96i will be cast to short.

info

Type casts that cause data loss will cause entire line to be rejected.

Cast table#

The following cast operations are supported when existing table column type is not long:

byteshortintlongfloatdoubledatetimestamp
integercastcastcastnativecastcastcastcast

Long256#

Custom type, which correspond to QuestDB type long256. The values are hex encoded 256-bit unsigned integer values with i suffix. For example:

temps,device=cpu,location=south value=0x123a4i 1638202821000000000\n

When column does not exist, it will be created with type long256. Values overflowing 256-bit integer will cause the entire line to be rejected.

long256 cannot be cast to anything else.

Float#

These values correspond to QuestDB type double. They actually do not have any suffix, which might lead to a confusion. For example:

trade,ticker=BTCUSD price=30 1638202821000000000\n

price value will be stored as double even though it does not look like a conventional double value would.

Cast table#

The following cast operations are supported when existing table column type is not double:

floatdouble
floatcastnative

Boolean#

These value correspond to QuestDB type boolean. In InfluxDB Line Protocol boolean values can be represented in any of the following ways:

Actual valueSingle char lowercaseSingle char uppercaseFull lowercaseFull camelcaseFull uppercase
truetTtrueTrueTRUE
falsefFfalseFalseFALSE

Example:

sensors,location=south warning=false\n

Cast table#

The following cast operations are supported when existing table column type is not boolean:

booleanbyteshortintfloatlongdouble
booleannativecastcastcastcastcastcast

When cast to numeric type, boolean true is 1 and false is 0

String#

These value correspond to QuestDB type string. They must be enclosed in quotes. Quotation marks " in values must be escaped using \. For example:

trade,ticker=BTCUSD description="this is a \"rare\" value",user="John" 1638202821000000000\n

The result:

timestamptickerdescriptionuser
1638202821000000000BTCUSDthis is a "rare" valueJohn
String values must be UTF8 encoded before sending. :::

Cast table#

The following cast operations are supported when existing table column type is not string:

charstringgeohashsymbol
stringcastnativecastno

Cast to CHAR#

String value can be cast to char type if its length is less than 2 characters. The following example are valid lines:

trade,ticker=BTCUSD status="A" 1638202821000000000\n
trade,ticker=BTCUSD status="" 1638202821000000001\n

The result:

timestamptickerstatus
1638202821000000000BTCUSDA
1638202821000000001BTCUSDnull

Casting strings with 2 or more characters to char will cause entire line to be rejected.

Cast to GEOHASH#

String value can be cast to geohash type when the destination column exists and is of a GEOHASH type already. Do make sure that column is created upfront. Otherwise, ILP will create STRING column regardless of the value.

Example:

Upcasting is an attempt to store higher resolution geohash in a lower resolution column. Let's create table before sending ILP message. Our geohash column has resolution of 4 bits.

create table tracking (geohash GEOHASH(4b), ts timestamp) timestamp(ts) partition by hour;

Send message including 16c geohash value:

tracking,obj=VLCC\ STEPHANIE gh="9v1s8hm7wpkssv1h" 1000000000\n

The result. geohash value has been truncated to size of the column.

tsgh
1970-01-01T00:00:01.000000Z0100

Sending empty string value will insert null into geohash column of any size:

tracking,obj=VLCC\ STEPHANIE gh="" 2000000000\n
tsgh
1970-01-01T00:00:01.000000Znull

values into higher resolution column, will cause the entire line to be rejected. :::

Timestamp#

These value correspond to QuestDB type timestamp. Timestamp values are epoch microseconds suffixed with t. In this example we're populating non-designated timestamp field ts1:

tracking,obj=VLCC\ STEPHANIE gh="9v1s8hm7wpkssv1h",ts1=10000t 1000000000\n

It is possible to populate designated timestamp using columnset, although this is not recommended. Let's see how this works in practice. Assuming table:

CREATE TABLE (loc SYMBOL, ts timestamp) TIMESTAMP(ts) PARTITION BY DAY;

When we send:

Sending mixed desginated timestamp values
tracking,loc=north ts=2000000000t 1000000000\n
tracking,loc=south ts=3000000000t\n

The result in columnset value always wins:

locts
north2000000000
south3000000000