NEW and OLD record access in PostgreSQL - Deep Dive

Overview - NEW and OLD record access

What is it?

In PostgreSQL triggers, NEW and OLD are special records that let you see the data before and after a change. OLD holds the row data before an update or delete, while NEW holds the row data after an insert or update. These allow you to compare or modify data during trigger execution. They are essential for writing triggers that react to data changes.

Why it matters

Without NEW and OLD, you couldn't track or react to changes in your database rows automatically. This would make it hard to enforce rules, audit changes, or maintain data integrity. They let the database itself watch and respond to data changes, saving time and preventing errors.

Where it fits

You should know basic SQL commands like INSERT, UPDATE, and DELETE before learning about NEW and OLD. After this, you can learn how to write triggers and functions that automate database tasks or enforce complex rules.

Mental Model

Core Idea

NEW and OLD are snapshots of a database row before and after a change, letting triggers see what changed.

Think of it like...

Imagine editing a document: OLD is the version before you made changes, and NEW is the version after. You can compare both to see what you changed.

┌─────────────┐       ┌─────────────┐
│   OLD Row   │──────▶│ Trigger     │
│ (before)    │       │ accesses    │
└─────────────┘       │ OLD and NEW │
                      │ records     │
┌─────────────┐       │             │
│   NEW Row   │──────▶│             │
│ (after)     │       └─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding database row changes

Concept: Rows in a database table can be inserted, updated, or deleted, changing the data stored.

When you add a new row, that's an INSERT. When you change data in a row, that's an UPDATE. When you remove a row, that's a DELETE. Each operation changes the data stored in the table.

Result

You know the three main ways data changes in a table: insert, update, delete.

Understanding these operations is key because NEW and OLD relate directly to these changes.

2

Foundation

What triggers do in PostgreSQL

Concept: Triggers run special code automatically when data changes happen in a table.

A trigger is like a watchful helper that runs a function when you insert, update, or delete rows. This function can check, change, or log data.

Result

You understand that triggers automate reactions to data changes.

Knowing triggers exist sets the stage for why NEW and OLD records are needed.

3

Intermediate

Role of OLD record in triggers

🤔Before reading on: do you think OLD is available during INSERT operations? Commit to yes or no.

Concept: OLD holds the data of a row before it is changed or deleted.

During an UPDATE or DELETE, OLD contains the row's data before the change. For INSERT, OLD is not available because the row didn't exist before.

Result

You can access the previous state of a row during updates and deletes using OLD.

Understanding when OLD is available helps avoid errors and write correct triggers.

4

Intermediate

Role of NEW record in triggers

🤔Before reading on: do you think NEW is available during DELETE operations? Commit to yes or no.

Concept: NEW holds the data of a row after it is inserted or updated.

During INSERT and UPDATE, NEW contains the new row data. For DELETE, NEW is not available because the row is being removed.

Result

You can access the new state of a row during inserts and updates using NEW.

Knowing when NEW is available lets you safely use it to modify or check new data.

5

Intermediate

Using NEW and OLD in BEFORE triggers

🤔Before reading on: do you think you can modify NEW in a BEFORE trigger to change the data saved? Commit to yes or no.

Concept: In BEFORE triggers, you can read OLD and NEW and even change NEW to affect the final data.

BEFORE triggers run before the data change happens. You can compare OLD and NEW to enforce rules or modify NEW to change what gets saved.

Result

You can control data changes before they happen by modifying NEW in BEFORE triggers.

Understanding this lets you enforce complex validations or defaults automatically.

6

Advanced

NEW and OLD in AFTER triggers

🤔Before reading on: do you think modifying NEW in AFTER triggers changes the saved data? Commit to yes or no.

Concept: AFTER triggers run after the data change and can read OLD and NEW but cannot modify the data saved.

AFTER triggers are for actions like logging or cascading changes. You can see OLD and NEW but changing NEW has no effect on the stored data.

Result

You know AFTER triggers are for side effects, not changing data.

Knowing this prevents confusion and bugs when writing triggers.

7

Expert

Handling NULL and missing NEW/OLD records

🤔Before reading on: do you think NEW and OLD are always non-null in all trigger types? Commit to yes or no.

Concept: Depending on the trigger event, NEW or OLD may be NULL or unavailable, and you must handle this safely.

For example, in INSERT triggers, OLD is NULL; in DELETE triggers, NEW is NULL. Trying to access fields in NULL records causes errors. You must check for NULL before use.

Result

You avoid runtime errors by correctly handling NULL NEW or OLD records.

Understanding this subtlety is crucial for robust trigger functions in production.

Under the Hood

When a data change happens, PostgreSQL creates temporary records representing the row before and after the change. OLD holds the pre-change row, NEW holds the post-change row. The trigger function receives these as special variables. Depending on the trigger timing (BEFORE or AFTER) and event (INSERT, UPDATE, DELETE), one or both may be NULL. The database engine manages these records in memory during the trigger execution.

Why designed this way?

This design allows triggers to have full context of what changed, enabling complex logic like validation, auditing, or cascading updates. Using OLD and NEW as records avoids copying entire tables and keeps triggers efficient. Alternatives like only passing changed fields would limit flexibility. This approach balances power and performance.

┌───────────────┐
│ Data Change   │
│ (INSERT/UPD/DEL)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ PostgreSQL    │
│ creates OLD & │
│ NEW records   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Trigger Func  │
│ accesses OLD  │
│ and NEW vars  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Data Change   │
│ applied or    │
│ side effects  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Is OLD available during INSERT triggers? Commit to yes or no.

Common Belief: OLD is always available in any trigger because it represents the row.

Reality: OLD is NULL during INSERT triggers because the row did not exist before.

Why it matters: Assuming OLD exists during INSERT causes runtime errors and broken triggers.

Quick: Can you modify NEW in AFTER triggers to change saved data? Commit to yes or no.

Common Belief: Modifying NEW in any trigger changes the data saved to the table.

Reality: Modifying NEW in AFTER triggers has no effect because the data is already saved.

Why it matters: Trying to change NEW in AFTER triggers leads to confusion and wasted effort.

Quick: Does NEW exist during DELETE triggers? Commit to yes or no.

Common Belief: NEW always exists because it represents the new state of the row.

Reality: NEW is NULL during DELETE triggers because the row is being removed.

Why it matters: Accessing NEW during DELETE triggers without checks causes errors.

Quick: Are NEW and OLD copies of the entire table? Commit to yes or no.

Common Belief: NEW and OLD are full copies of the whole table for each change.

Reality: They are just single-row records representing the affected row, not the whole table.

Why it matters: Misunderstanding this leads to inefficient trigger designs and performance issues.

Expert Zone

1

In row-level triggers, NEW and OLD are per-row, but in statement-level triggers, they are not available, which changes how you write triggers.

2

Modifying NEW in BEFORE triggers can prevent infinite recursion if the trigger updates the same table carefully.

3

Triggers using NEW and OLD must handle NULL values gracefully to avoid runtime errors, especially in complex multi-event triggers.

When NOT to use

Do not use NEW and OLD in statement-level triggers because they do not have row context. Instead, use transition tables introduced in PostgreSQL 10 for set-based operations. Also, avoid triggers for simple constraints better handled by CHECK constraints or foreign keys.

Production Patterns

In production, NEW and OLD are used for auditing changes by logging differences, enforcing business rules by validating or modifying data before saving, and implementing soft deletes by changing NEW values in BEFORE DELETE triggers.

Connections

Event-driven programming

NEW and OLD records in triggers are like event payloads in event-driven systems that carry state before and after an event.

Understanding NEW and OLD helps grasp how systems react to changes by receiving context about what changed, a core idea in event-driven design.

Version control systems

OLD and NEW records are similar to previous and current versions of files in version control.

This connection clarifies how databases track changes at the row level, just like version control tracks changes in files.

Undo/Redo mechanisms in software

OLD and NEW provide the data snapshots needed to undo or redo changes in a database.

Knowing this link shows how triggers can support rollback or audit features by comparing OLD and NEW states.

Common Pitfalls

#1 Accessing OLD in an INSERT trigger causes an error.

Wrong approach:

CREATE FUNCTION trg_func() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'Old id: %', OLD.id; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg BEFORE INSERT ON mytable FOR EACH ROW EXECUTE FUNCTION trg_func();

Correct approach:

CREATE FUNCTION trg_func() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'New id: %', NEW.id; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg BEFORE INSERT ON mytable FOR EACH ROW EXECUTE FUNCTION trg_func();

Root cause:

Misunderstanding that OLD is NULL during INSERT triggers leads to referencing a NULL record.

#2 Trying to modify NEW in an AFTER trigger expecting data change.

Wrong approach:

CREATE FUNCTION trg_func() RETURNS trigger AS $$ BEGIN NEW.name := 'Changed'; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg AFTER UPDATE ON mytable FOR EACH ROW EXECUTE FUNCTION trg_func();

Correct approach:

CREATE FUNCTION trg_func() RETURNS trigger AS $$ BEGIN PERFORM log_change(OLD.id, NEW.name); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg AFTER UPDATE ON mytable FOR EACH ROW EXECUTE FUNCTION trg_func();

Root cause:

Not knowing AFTER triggers run after data is saved, so modifying NEW has no effect.

#3 Not checking for NULL NEW or OLD in triggers handling multiple events.

Wrong approach:

CREATE FUNCTION trg_func() RETURNS trigger AS $$ BEGIN IF NEW.id = 0 THEN RAISE EXCEPTION 'Invalid'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

Correct approach:

CREATE FUNCTION trg_func() RETURNS trigger AS $$ BEGIN IF TG_OP = 'DELETE' THEN RETURN OLD; ELSIF NEW.id IS NULL THEN RAISE EXCEPTION 'Invalid'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

Root cause:

Assuming NEW and OLD always exist leads to runtime errors when one is NULL.

Key Takeaways

NEW and OLD are special records in PostgreSQL triggers representing row data after and before changes.

OLD is available in UPDATE and DELETE triggers; NEW is available in INSERT and UPDATE triggers.

In BEFORE triggers, you can modify NEW to change data before saving; in AFTER triggers, NEW is read-only.

Always check for NULL NEW or OLD depending on the trigger event to avoid errors.

Understanding NEW and OLD is essential for writing effective triggers that automate data validation, auditing, and business logic.