Insert into from select with conflict update set

CrateDB
1

Hi all,

This is a sanity check really.

I’m doing an aggregation insert from a select using avg.max and max_by (downsampling) in the select grouped by a date_bin.
I want to do an on conflict update set to update the data if the source select data has changed.

Is it correct in the update set to do this :-

I

NSERT INTO <Table>
 ae1,
 e,
....
( SELECT
    avg( ae1),
    MAX_BY(e,ts),
    ...  
 )
ON CONFLICT UPDATE SET 
          ae1 = avg( excluded.ae1 ),
          e     = MAX_BY( excluded.e,timestamp ),
...

Is this correct ?

Many thanks
David

2

Hi David,
The syntax you are looking for would be something like:

INSERT INTO <Table> ( ae1, e,....)
SELECT
    avg( ae1) ,
    MAX_BY(e,ts) ,
    ...  
ON CONFLICT (<PKcolumns of Table>) DO UPDATE SET 
          ae1 = excluded.ae1 ,
          e     = excluded.e,
		  ... ;

The fields in excluded are the values that could not be inserted so they are already aggregated, but please do a few tests before putting this into production.
This approach requires that you still have available all the data to compute the downsampled values for the period.
An alternative approach would be to keep a “weight” in the table with the downsampled data, that way when a new value is added to the period or a value changes you can amend the downsampled value, but aggregations are so fast in CrateDB that if the data is still available it may not be worth the additional complexity.

1 Like