by Vinicius Negrisolo on October 30, 2025

PostgreSQL 18 introduces a powerful new feature that solves a long standing limitation: accessing both the previous and updated values in RETURNING clauses. Let's explore how this game changing addition simplifies complex UPSERT operations.

Up to PostgreSQL 17 we could use a RETURNING * or RETURNING id, email by the end of statements like INSERT, UPDATE, DELETE or MERGE which is very useful. That would return as a regular SELECT fashion the values that you just inserted, updated or the ones that you just deleted. For me this was great already for the INSERT and DELETE cases, but when updating I wish sometimes I could have access to what was the old values we just updated from.

In a previous post I talked about UUID v7 in PostgreSQL 18, and now I want to talk about a new way to get previous data before an UPDATE or an UPSERT. I am talking about the new and old content of the modified rows.

For this post specifically I'll focus on the UPSERT approach as they are usually a bit more complex and we can infer how to use this new addition in the UPDATE anyway.

First thing first, UPSERT is not a valid SQL standard command, nor a PostgreSQL command, but it refers to run a command with some logic that decides if it's an INSERT or an UPDATE operation.

The MERGE operation was added to the SQL standard back in 2003 and I believe it should be the official way to do UPSERTs, but they were only added in PostgreSQL many years after, just in the version 15 which was released in October 2022. And as a result of that the libraries we use as data layers in our applications might not support the merge command yet. Here's how you use the MERGE in PostgreSQL.

The alternative to this is to use INSERT with a ON CONFLICT target. This operation is a bit more restrict than MERGE so you can eventually convert your INSERT ... ON CONFLICT to a MERGE, but not necessarily the other way around. My examples here will be using the ON CONFLICT approach as I can adapt in my application data layer.

Let's start with a simple users table with just 2 rows created so far:

CREATE TABLE users (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT (NOW() AT TIME ZONE 'utc')
);

INSERT INTO users (email, full_name) VALUES
  ('darth@example.com', 'Darth Vader'),
  ('luke@example.com', 'Luke Skywalker');

And let's say that eventually we get a new list of users that needed to be imported. And we might have a few business rules at this import: we may have to report how many users were created, which ones have they name updated, and maybe send a welcome email to new users only.

The INSERT ... ON CONFLICT requires an UNIQUE index, and in all cases we considered to use that the unique was already there. In this case the email will be our uniqueness field that we'll use to try to update existing values, and insert otherwise.

As mentioned before, PostgreSQL 18 has now the OLD and NEW keywords to be used in the RETURNING clause. Let's see how:

INSERT INTO users (email, full_name) VALUES
  ('darth@example.com', 'Darth Vader'),
  ('luke@example.com', 'Luke Vader'),
  ('princess@example.com', 'Princess Leia')
ON CONFLICT (email) DO UPDATE
SET full_name = EXCLUDED.full_name
RETURNING
  email,
  OLD.full_name AS old_full_name,
  NEW.full_name AS new_full_name,
  OLD.id IS NULL AS is_new_record,
  OLD.id IS NOT NULL AND OLD.full_name <> NEW.full_name AS has_changed;

And here's the result:

Alright, so all in 1 query. With that we could insert a new record, we could update another one, and do not touch the one that the value didn't change. We have now these booleans being returned so we can implement the business logic required and all of that was only possible with the new NEW and OLD content accessors.

This single query now gives you:

• Insert tracking: Identify new records with OLD.id IS NULL
• Update detection: Spot actual changes with comparison logic
• Audit trails: Capture before and after states in one operation
• Business logic: Send welcome emails only to new users, or trigger notifications only when values actually change

At Hashrocket, we specialize in building robust, scalable applications with cutting-edge technologies. Whether you're optimizing PostgreSQL databases, building real-time features with Elixir and Phoenix, creating full-stack applications with Ruby on Rails, or developing modern frontends with React and React Native, our team of expert developers can help. We stay on top of the latest features and best practices to deliver high-quality solutions tailored to your needs. Get in touch to see how we can accelerate your project.