NEW and OLD record access in PostgreSQL

Introduction

NEW and OLD let you see data before and after a change in a table. This helps you check or use the changed data inside triggers.

When you want to log changes made to a table automatically.

When you need to check if a value changed before allowing an update.

When you want to copy old data to another table before it gets updated or deleted.

When you want to enforce rules based on how data changes in a table.

Syntax

PostgreSQL

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

-- Inside the trigger function:
-- Use NEW.column_name to access new data (for INSERT or UPDATE)
-- Use OLD.column_name to access old data (for UPDATE or DELETE)

NEW is available in INSERT and UPDATE triggers to see the new row data.

OLD is available in UPDATE and DELETE triggers to see the old row data.

Examples

This trigger runs after an employee record is updated.

PostgreSQL

CREATE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_changes();

This function prints old and new salary values when an update happens.

PostgreSQL

CREATE OR REPLACE FUNCTION log_employee_changes() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Old salary: %, New salary: %', OLD.salary, NEW.salary;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Sample Program

This example creates a table and a trigger that prints a message when an employee's salary changes.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  salary INT
);

CREATE OR REPLACE FUNCTION log_salary_change() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Salary changed from % to % for employee %', OLD.salary, NEW.salary, NEW.name;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_change_trigger
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

INSERT INTO employees (name, salary) VALUES ('Alice', 50000);
UPDATE employees SET salary = 55000 WHERE name = 'Alice';

Important Notes

Triggers using NEW and OLD must be row-level (FOR EACH ROW) to access individual rows.

In INSERT triggers, OLD is not available because there is no old row.

In DELETE triggers, NEW is not available because the row is being removed.

Summary

NEW and OLD let you access data before and after changes inside triggers.

Use NEW for new data in INSERT and UPDATE triggers.

Use OLD for old data in UPDATE and DELETE triggers.