Can I replace null values in returned rows in QuestDB?

Question

Some aggregate queries are returning null values, is there a way to handle this?

select time, avg(temp_f) from sensor_data sample by 1h

If some records are missing, can the aggregate be set to something other than null?

Answer 1

• If your dataset has gaps of time and is missing entire records for a certain duration, you can use FILL(). You can choose a fill strategy, such as LINEAR for interpolation, PREV for filling with the previous value, or you can specify constants. This will include new rows in the returned response where there were gaps:

  SELECT time, avg(temp_f) 
  FROM sensor_data
  SAMPLE BY 1h FILL(50);
  

• If your dataset has records for a certain period, but a sensor was sending null instead of a value, you can use coalesce() to specify how null should be handled. No new rows are returned and a default is set for null values:

  SELECT time, coalesce(avg(temp_f), 50) 
  FROM sensor_data
  SAMPLE BY 1h;
  

For more information, see the FILL keyword documentation and for coalesce(), see the conditional functions documentation pages.