github.com/status-im/status-go@v1.1.0/walletdatabase/migrations/sql/1721136888_recreate_indices_balance_history_remove_dups.up.sql (about)

     1  -- Step 1: Create a temporary table to store unique records
     2  CREATE TABLE IF NOT EXISTS balance_history_temp AS
     3  SELECT DISTINCT chain_id, address, currency, block, MAX(timestamp) as timestamp, balance
     4  FROM balance_history
     5  GROUP BY chain_id, address, currency, block;
     6  
     7  -- Step 2: Truncate the original table
     8  DELETE FROM balance_history;
     9  
    10  -- Step 3: Insert unique records back into the original table
    11  INSERT INTO balance_history (chain_id, address, currency, block, timestamp, balance)
    12  SELECT chain_id, address, currency, block, timestamp, balance
    13  FROM balance_history_temp;
    14  
    15  -- Step 4: Drop the temporary table
    16  DROP TABLE balance_history_temp;
    17  
    18  -- Step 5: Recreate the indices
    19  CREATE UNIQUE INDEX IF NOT EXISTS balance_history_identify_entry ON balance_history (chain_id, address, currency, block);
    20  CREATE INDEX IF NOT EXISTS balance_history_filter_entries ON balance_history (chain_id, address, currency, block, timestamp);