TimescaleDB/Postgres: INSERT ON CONFLICT KEEP MAXIMUM

user_2728815 0
Viewed 259

My table is defined as: (it is a simple metric-config tracking one)

        CREATE TABLE IF NOT EXISTS strategy_registry (
        name            VARCHAR     NOT NULL,
        symbol          VARCHAR     NOT NULL,
        predictor_id    VARCHAR     NOT NULL,
        params          jsonb       NOT NULL,
        metric          FLOAT       NOT NULL,
        PRIMARY KEY(name, symbol, predictor_id)
        );

And on insert conflict, I want to keep the maximum metric and the params column, of the version that got the maximum metric value. I am using TimescaleDB (postgres 12)

    INSERT INTO strategy_registry (name, symbol, predictor_id, params, metric)
    VALUES ({{ name }}, {{ symbol }}, {{ predictor_id }}, {{ params }}, {{ metric }})
    ON CONFLICT (name, symbol, predictor_id) 
    DO UPDATE SET 
    (metric, params) = max(existing, excluded), params of greatest `metric` column value;
timescaledb postgresql sql
3 Answers

The function you looking for is greatest(...) not max. Max is a columnar function that works on the entire set of rows, in this case in the table. Greatest chooses the 'maximum' value from a list of values (or variables). Example:

select greatest(1,4,3,5,7,0)

returns 7. It is also usable in an assignment as

something = greatest(...);
user_76238560

I think the other answers should work, however, I would say that it is probably not the most efficient way to achieve this. Instead I would use the WHERE clause in the update:

ON CONFLICT (name, symbol, predictor_id)  DO UPDATE SET
metric = excluded.metric, params = excluded.params
WHERE excluded.metric > metric

The previous answer can do the update even when the value stays the same and cause table bloat and extra work (also, I think it's clearer semantically).

user_91946570

I do not think you can handle both columns at the same time. Something like this should work:

INSERT INTO strategy_registry (name, symbol, predictor_id, params, metric)
VALUES ({{ name }}, {{ symbol }}, {{ predictor_id }}, {{ params }}, {{ metric }})
  ON CONFLICT (name, symbol, predictor_id) 
    DO UPDATE 
      SET params = CASE 
                     WHEN metric < EXCLUDED.metric THEN EXCLUDED.params
                     ELSE params
                   END,
          metric = GREATEST(metric, EXCLUDED.metric);
user_138083190
Related