Merge Statement Postgres

merge.sql

-- Create the deposit_interest_rates table
CREATE TABLE deposit_interest_rates (
    id UUID PRIMARY KEY,
    bank_id UUID NOT NULL,
    term INTEGER NOT NULL,
    interest_rate DECIMAL(5,2) NOT NULL,
    inserted_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
);

-- Create a unique constraint to prevent duplicate bank_id/term combinations
CREATE UNIQUE INDEX deposit_interest_rates_bank_term_idx 
ON deposit_interest_rates (bank_id, term);

-- Insert some initial data
INSERT INTO deposit_interest_rates (
    id, bank_id, term, interest_rate, inserted_at, updated_at
) VALUES (
    'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid,
    'b1eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid,
    12,
    5.25,
    '2024-01-01 00:00:00',
    '2024-01-01 00:00:00'
);

-- Example of MERGE operation with new and updated values
WITH new_values (id, bank_id, term, interest_rate, inserted_at, updated_at) AS (
    VALUES 
        -- Update existing record (same bank_id and term, new interest rate)
        ('c0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid,
         'b1eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid,
         12,
         5.75,
         '2024-01-01 00:00:00',
         '2024-01-02 00:00:00'),
        -- Insert new record (different bank_id)
        ('d0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid,
         'c1eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid,
         12,
         5.50,
         '2024-01-02 00:00:00',
         '2024-01-02 00:00:00')
)
MERGE INTO deposit_interest_rates t
USING new_values n
ON t.bank_id = n.bank_id AND t.term = n.term
WHEN MATCHED THEN
    UPDATE SET
        interest_rate = n.interest_rate,
        updated_at = n.updated_at
WHEN NOT MATCHED THEN
    INSERT (id, bank_id, term, interest_rate, inserted_at, updated_at)
    VALUES (
        n.id,
        n.bank_id,
        n.term,
        n.interest_rate,
        n.inserted_at,
        n.updated_at
    );

-- Query to show the results
SELECT 
    id,
    bank_id,
    term,
    interest_rate,
    inserted_at,
    updated_at
FROM deposit_interest_rates
ORDER BY bank_id, term;