UPSERT: Insert-or-Update Atomically

-- PostgreSQL UPSERT: insert or update daily balance
INSERT INTO daily_balance (account_id, balance_date, total_amount)
VALUES ('SAV2001', '2024-01-15', 50000.00)
ON CONFLICT (account_id, balance_date)
DO UPDATE SET total_amount = daily_balance.total_amount + EXCLUDED.total_amount;

Expected output:
INSERT 0 1
-- If the row already existed, total_amount is incremented by 50000.00
-- If the row did not exist, a new row is created with 50000.00

-- DANGEROUS: race condition in concurrent transfers
-- Transaction A and Transaction B both run this at the same time
SELECT total_amount FROM daily_balance
WHERE account_id = 'SAV2001' AND balance_date = '2024-01-15';

-- Both see: no row exists
-- Both execute:
INSERT INTO daily_balance (account_id, balance_date, total_amount)
VALUES ('SAV2001', '2024-01-15', 50000.00);
-- Transaction A succeeds. Transaction B gets: ERROR duplicate key.

-- PostgreSQL: accumulate daily transaction totals per account
INSERT INTO daily_balance (account_id, balance_date, total_amount, txn_count)
VALUES ('SAV2001', '2024-01-15', 75000.00, 1)
ON CONFLICT (account_id, balance_date)
DO UPDATE SET
  total_amount = daily_balance.total_amount + EXCLUDED.total_amount,
  txn_count    = daily_balance.txn_count + EXCLUDED.txn_count;

-- Skip duplicate customer imports silently
INSERT INTO customers (pan, full_name, branch)
VALUES ('ABCDE1234F', 'Priya Sharma', 'Mumbai-Central')
ON CONFLICT (pan) DO NOTHING;

-- MySQL: upsert daily balance
INSERT INTO daily_balance (account_id, balance_date, total_amount)
VALUES ('SAV2001', '2024-01-15', 50000.00)
ON DUPLICATE KEY UPDATE
  total_amount = total_amount + VALUES(total_amount);

-- SQL-standard MERGE: reconcile partner bank feed
MERGE INTO accounts AS target
USING partner_feed AS source
ON target.account_id = source.account_id
WHEN MATCHED AND source.status = 'CLOSED' THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET target.balance = source.balance,
             target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (account_id, balance, status, created_at)
  VALUES (source.account_id, source.balance, 'ACTIVE', CURRENT_TIMESTAMP);

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DailyBalanceService {

    // WRONG: race condition under concurrency
    public void updateBalanceUnsafe(Connection conn, String accountId,
                                     BigDecimal amount) throws SQLException {
        var rs = conn.prepareStatement(
            "SELECT total_amount FROM daily_balance WHERE account_id = ? AND balance_date = CURRENT_DATE"
        ).executeQuery();
        if (rs.next()) {
            // Another thread may have inserted between SELECT and UPDATE
            conn.prepareStatement(
                "UPDATE daily_balance SET total_amount = total_amount + ? WHERE account_id = ? AND balance_date = CURRENT_DATE"
            ).executeUpdate();
        } else {
            // Another thread may also see 'no row' and try INSERT
            conn.prepareStatement(
                "INSERT INTO daily_balance (account_id, balance_date, total_amount) VALUES (?, CURRENT_DATE, ?)"
            ).executeUpdate();
        }
    }

    // CORRECT: atomic upsert, no race condition
    public void updateBalanceSafe(Connection conn, String accountId,
                                   BigDecimal amount) throws SQLException {
        String sql = """
            INSERT INTO daily_balance (account_id, balance_date, total_amount)
            VALUES (?, CURRENT_DATE, ?)
            ON CONFLICT (account_id, balance_date)
            DO UPDATE SET total_amount = daily_balance.total_amount + EXCLUDED.total_amount
            """;
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);
            ps.setBigDecimal(2, amount);
            ps.executeUpdate();
        }
    }
}

-- Conditional upsert: only update if the new amount is larger
INSERT INTO daily_balance (account_id, balance_date, total_amount)
VALUES ('SAV2001', '2024-01-15', 50000.00)
ON CONFLICT (account_id, balance_date)
DO UPDATE SET total_amount = EXCLUDED.total_amount
WHERE EXCLUDED.total_amount > daily_balance.total_amount;

-- Sequence gap demonstration
CREATE TABLE loan_applications (
  id SERIAL PRIMARY KEY,
  pan VARCHAR(10) UNIQUE,
  amount NUMERIC(15,2)
);

-- First insert: id = 1
INSERT INTO loan_applications (pan, amount) VALUES ('ABCDE1234F', 500000.00)
ON CONFLICT (pan) DO UPDATE SET amount = EXCLUDED.amount;

-- Second insert with same PAN: id 2 is consumed but conflict routes to update
INSERT INTO loan_applications (pan, amount) VALUES ('ABCDE1234F', 750000.00)
ON CONFLICT (pan) DO UPDATE SET amount = EXCLUDED.amount;

-- Next new PAN gets id = 3 (id 2 is a gap)
INSERT INTO loan_applications (pan, amount) VALUES ('FGHIJ5678K', 300000.00)
ON CONFLICT (pan) DO UPDATE SET amount = EXCLUDED.amount;

Expected result:
 id |    pan     |  amount
----+------------+-----------
  1 | ABCDE1234F | 750000.00
  3 | FGHIJ5678K | 300000.00
-- id 2 is a gap, which is normal

-- Deadlock scenario:
-- Transaction A: UPSERT account SAV2001, then SAV2002
-- Transaction B: UPSERT account SAV2002, then SAV2001
-- Each holds one row lock and waits for the other.

-- Prevention: always upsert in sorted account_id order
-- Application code sorts the batch before executing

-- WRONG: no unique constraint on (account_id, balance_date)
CREATE TABLE daily_balance_bad (
  account_id VARCHAR(20),
  balance_date DATE,
  total_amount NUMERIC(15,2)
);

INSERT INTO daily_balance_bad (account_id, balance_date, total_amount)
VALUES ('SAV2001', '2024-01-15', 50000.00)
ON CONFLICT (account_id, balance_date) DO UPDATE SET total_amount = EXCLUDED.total_amount;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

-- CORRECT: add the unique constraint first
CREATE TABLE daily_balance_good (
  account_id VARCHAR(20),
  balance_date DATE,
  total_amount NUMERIC(15,2),
  UNIQUE (account_id, balance_date)
);