Search code examples
databasetime-seriesquestdb

Sending ints and floats to QuestDB from the C# client

We use decimals for financial data. I do see Quest supports float and double. Seems to me like a waste to use double, so I want to store as float. Same with int and long.

I am using this code with the QuestDB sender to ingest the columns

.Column(s_RateColumns[nameof(rate.Ask)], (float)rate.Ask)
.Column(s_RateColumns[nameof(rate.Bid)], (float)rate.Bid)
.Column(s_RateColumns[nameof(rate.Value)], (float)rate.Value)
.Column(s_RateColumns[nameof(rate.State)], (int)rate.State)

Yet with a persist of float type in DB I still get double. and on int I get long.

Solution

  • The C# client, as all the other QuestDB official clients, uses the ILP protocol to send the data to the server. ILP is a text protocol and assumes 64 bit numbers. The data is sent in UTF-8 format, and it doesn't matter whether it's a float or double client side.

    If a table already exist with the name you are sending, then QuestDB will convert the columns into the types defined in the table, as long as they are compatible, otherwise it would raise an error.

    If the table does not exist, or if one of the columns you are sending does not exist, QuestDB will automatically create a new table or a new column using defaults. This is standard in tools compatible with the ILP protocol, as it allows for some dynamic schemas on environments where new columns can be occasionally created.

    While automatic table and column creation are convenient, they use default values, and as such it might not be ideal. In this case, even if you are sending client-side a float and an integer, on the receiving end QuestDB will just see a number as a string and will create a Double or a Long depending on if it has decimals or not.

    If you want to make sure the numbers are stored as the data types you expect, you can always issue a CREATE TABLE or CREATE TABLE IF NOT EXISTS statement before ingesting data. This can be done either using a postgres-compatible library, or directly by calling the /exec endpoint of the QuestDB REST API.

    If this was for batch uploading, you could also use the /imp endpoint of the REST API, which expects a CSV and accepts an optional schema definition.