QuestDB column definition check constrains, NOT NULL and CHECK(condition/s)

Question

I am using QuestDB to store temperature and humidity measurements from a few hundred sensors I have on the field. Each sensor is uniquely identified by an id, and produces a measurement (an insert) every minute.

Here is my table definition:

create table sensors(id symbol, 
                     temperature int, 
                     humidity int, 
                     ts timestamp) 
    timestamp(ts) partition by MONTH;

I need to reject not valid inserts.

A valid insert is defined as:

• id: cannot be null
• humidity: must be a value between 0 and 100, but it only is useful if temperature is greater than 25C.

Is there any way to express these constraints in QuestDB?, for instance could I do:

create table sensors(id symbol not null, 
                     temperature int, 
                     humidity int check (humidity >= 0 and humidity <= 100 and temperature > 25), 
                     ts timestamp) 
    timestamp(ts) partition by MONTH;

Answer 1

In standard SQL, there are a few ways to define constraints (some of which appear in the example):

1. Column constraints, which are set as column properties, such as NOT NULL. Looking at QuestDB's supported syntax for CREATE TABLE, these aren't supported.
2. Check constraints, which allow for arbitrary boolean-valued expressions. Again, QuestDB's CREATE TABLE syntax shows no support for these.
3. Index constraints. Some indices restrain data values, such as not allowing NULLs for primary keys. Currently, QuestDB only supports indices on SYMB columns (which works for the schema in question), but does not appear to constrain column values (i.e. the index is solely for query efficiency, not data integrity).
4. Triggers, which allow code to be executed on events, usually when DML queries are executed (inserts, updates and deletes), and can abort data changes. The QuestDB SQL reference makes no mention of triggers.

Note: under the hood, some of the above might not differ (e.g. some column constraints, such as PRIMARY, could be translated to index constraints).

The usual ways of constraining data aren't available, but there are other options. One would be to create a view and query that, rather than the original table. Unfortunately, QuestDB doesn't support views.

This leaves one option: check the data before passing it to the database. Generally, you'd avoid having host code be responsible for database tasks, but that's assuming the DBMS supports those tasks. If some capability is entirely missing from a DBMS, accomplishing it in the host code is the only way.