Why NEW and OLD record access in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if your database could remember every change automatically, saving you from costly mistakes?

The Scenario

Imagine you are updating a large spreadsheet by hand, trying to remember the old values before changing them. You want to compare what was there before and what you are changing it to, but you have no easy way to track the old data.

The Problem

Manually tracking old and new values is slow and confusing. You might forget what the original data was, make mistakes, or lose track of changes. This leads to errors and wasted time, especially when many records are involved.

The Solution

Using NEW and OLD record access in database triggers lets you automatically see the data before and after a change. This makes it easy to compare, validate, or log changes without manual effort.

Before vs After

✗ Before

UPDATE table SET column = new_value; -- no easy way to see old value

✓ After

CREATE TRIGGER trg BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION func(); -- inside func(), use OLD.column and NEW.column

What It Enables

This concept enables automatic tracking and handling of data changes, making updates safer and more reliable.

Real Life Example

In a banking app, when a user updates their address, triggers using OLD and NEW can log the old address and new address for audit and security.

Key Takeaways

Manual tracking of old and new data is error-prone and slow.

NEW and OLD record access in triggers automate this process.

This leads to safer, clearer, and more reliable data updates.