I have a flow which dynamically creates SQL params and updates some values in a table.
Depending on the some criteria, I need to update the daily, weekly, monthly, yearly or all time high score. Currently I have the following SQL code (prepared):
INSERT INTO liquidation_high_scores ( exchange, symbol, currency, day_high_score, week_high_score, month_high_score, year_high_score, all_time_high_score ) values ( $exchange, $symbol, $currency, $day_high_score, $week_high_score, $month_high_score, $year_high_score, $all_time_high_score ) ON CONFLICT(exchange, symbol, currency) DO UPDATE SET exchange = $exchange, symbol = $symbol, currency = $currency, day_high_score = $day_high_score, week_high_score = $week_high_score, month_high_score = $month_high_score, year_high_score = $year_high_score, all_time_high_score = $all_time_high_score;
My table looks like this:
CREATE TABLE liquidation_high_scores ( exchange TEXT, symbol TEXT, currency TEXT, day_high_score INTEGER, week_high_score INTEGER, month_high_score INTEGER, year_high_score INTEGER, all_time_high_score INTEGER, PRIMARY KEY (exchange, symbol, currency) )
The issue is, unless a param is populated, i.e. only
day_high_score is included in the params, the other values are overwritten with NULL. To workaround this, I've created a function with 5 outputs (one for day, week, month, year, all_time), and have connected the SQL node to each with code containing only the correct params. Obviously this is a little messy to manage, I wondered if there's a better way to achieve this, perhaps insert a value ONLY if included in the params, and avoid writing NULL for ones which are not?
I'm also not convinced my table is setup right, in essence, the combination of exchange, symbol and currency should be unique to each row... Duplicates should not be allowed.
Many thanks in advance for any help.