PostgreSQL

Chapter 6 - DML (Data Manipulation Language)

UPSERT

In PostgreSQL, the UPSERT operation allows you to either insert a new row into a table or update an existing one if a conflict arises (e.g., a duplicate key). This is particularly useful when managing banking data, such as customer details, accounts, or transactions, where you want to avoid duplicates but still modify existing records efficiently. The UPSERT functionality is implemented using the INSERT ... ON CONFLICT clause. This helps in scenarios where you want to ensure data integrity by avoiding duplicates while also having the flexibility to update specific columns when conflicts arise.

Here’s how you can use UPSERT in PostgreSQL, particularly for banking scenarios like managing customers, accounts, and transactions.

Creating a table forcustomers

Before using UPSERT, let's define a customers table that holds basic customer details for a banking system.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone_number VARCHAR(15),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Using theINSERT ... ON CONFLICTfor UPSERT

To perform an UPSERT, you use the INSERT command along with the ON CONFLICT clause. The ON CONFLICT clause tells PostgreSQL how to handle conflicts, typically on a UNIQUE constraint.

INSERT INTO customers (name, email, phone_number)
VALUES ('John Doe', 'john.doe@example.com', '555-1234')
ON CONFLICT (email)
DO UPDATE SET phone_number = EXCLUDED.phone_number;

If the email already exists, PostgreSQL will update the phone_number for that record instead of inserting a duplicate.
If the email does not exist, a new customer record will be inserted.

Creating a table for accounts

Now, let's define a table for storing customer bank accounts.

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    account_number VARCHAR(20) UNIQUE NOT NULL,
    balance NUMERIC(12, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

PerformingUPSERTonaccountstable

If a customer’s account number is already present in the system, we will update the balance; otherwise, we will insert a new account.

INSERT INTO accounts (customer_id, account_number, balance)
VALUES (1, '123456789', 5000.00)
ON CONFLICT (account_number)
DO UPDATE SET balance = EXCLUDED.balance;

The ON CONFLICT targets the account_number, ensuring that duplicate account numbers are avoided. If a conflict occurs, the account balance will be updated instead.

Creating a table for transactions

For handling transactions between accounts, we can create a table to log details like the amount, source, and destination accounts.

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INT REFERENCES accounts(account_id),
    transaction_type VARCHAR(10) CHECK (transaction_type IN ('debit', 'credit')),
    amount NUMERIC(12, 2) NOT NULL,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Inserting transaction records using UPSERT

If needed, you can also apply UPSERT logic to the transactions, although typically, transactions are more frequently inserted rather than updated.

INSERT INTO transactions (account_id, transaction_type, amount)
VALUES (1, 'credit', 1000.00)
ON CONFLICT (transaction_id)
DO UPDATE SET amount = EXCLUDED.amount;

In this case, we handle potential conflicts based on transaction_id. If a conflict arises, the transaction amount will be updated.

This approach ensures that your banking application remains consistent while preventing duplicate data entries, especially for critical entities like customers, accounts, and transactions.

Tansy SQL Course - UPSERT - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found

PostgreSQL

UPSERT

Most visited