Right, so you’ve just shoved a bunch of data into a table. Congratulations. But now what? You probably want to know what actually got put in there, right? Maybe you need the auto-generated ID for a new user record to create their first order, or perhaps you just want confirmation that your carefully crafted JSONB payload didn’t get mangled on the way in. You could immediately run a SELECT query, but that’s clunky, inefficient, and frankly, a little desperate. It’s like mailing a package and then driving to the recipient’s house to ask if they got it.

PostgreSQL, in its infinite wisdom, gives you a far more elegant solution: the RETURNING clause. This is one of those features that, once you use it, you’ll wonder how you ever lived without it. It turns INSERT, UPDATE, and DELETE from silent workhorses into conversational partners. They do the job and immediately hand you back a full report of what they just did.

What RETURNING Actually Does

In the simplest terms, RETURNING appends a result set to your data-modifying query, just like a SELECT statement. It returns the rows that were affected by the operation. For an INSERT, this is the row(s) as they were inserted. This is incredibly powerful because it happens atomically within the same command. There’s no race condition where another transaction modifies the row between your insert and your subsequent select.

Let’s get our hands dirty. Imagine we have a products table.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10, 2),
    in_stock BOOLEAN DEFAULT TRUE
);

Now, let’s insert a new product and immediately get its generated id and confirmed name.

INSERT INTO products (name, price)
VALUES ('Organic Hyperloom Underwear', 49.99)
RETURNING id, name;

The output isn’t just “OK” or “1 row inserted”; it’s an actual result set:

 id |            name            
----+----------------------------
 42 | Organic Hyperloom Underwear
(1 row)

Boom. You now have the system-generated id (42) without a separate round trip to the database. Your application code can grab that value directly from the query result.

Using RETURNING with Multi-Row Inserts

This is where RETURNING goes from “neat” to “absolutely essential.” When you insert multiple rows in a single statement, RETURNING gives you the entire set of results. This is a massive performance win.

INSERT INTO products (name, price)
VALUES
    ('Self-Heating Coffee Mug', 24.99),
    ('Left-Handed Screwdriver', 8.50),
    ('Ubuntu Linux Plushie', 19.99)
RETURNING *;

This returns all columns for all three inserted rows:

 id |            name            | price | in_stock
----+----------------------------+-------+----------
 43 | Self-Heating Coffee Mug    | 24.99 | t
 44 | Left-Handed Screwdriver    |  8.50 | t
 45 | Ubuntu Linux Plushie      | 19.99 | t
(3 rows)

Need to process these new records in your application? You’ve got the entire dataset, right there. No need for a flurry of SELECT statements or convoluted logic to figure out which IDs were generated.

The Real Magic: RETURNING with ON CONFLICT (UPSERT)

This is the killer app for RETURNING. When you’re doing an “upsert” (INSERT ... ON CONFLICT ... DO UPDATE), things can get ambiguous. Did the statement insert a new row, or did it update an existing one? And what data ended up in the row afterwards? RETURNING is your only way to be sure.

Let’s add a unique constraint so we have something to conflict on.

ALTER TABLE products ADD CONSTRAINT unique_name UNIQUE (name);

Now, let’s try to insert a product that might already exist.

INSERT INTO products (name, price)
VALUES ('Ubuntu Linux Plushie', 15.99) -- New, lower price!
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price
RETURNING *, (xmax = 0) AS inserted;

Look at that RETURNING clause. It does two things:

  1. *: Returns all columns of the resulting row.
  2. (xmax = 0) AS inserted: This is a clever, semi-advanced trick. The xmax system column can (often) be used to infer if the row was newly inserted (xmax = 0) or was an existing row that was updated (xmax > 0). It’s not perfectly foolproof for every edge case, but it’s incredibly useful for most practical purposes.

Your result will tell you exactly what happened. If it was an update, you’ll see the new price (15.99). The inserted column will be false. If the row didn’t exist and was inserted, inserted would be true.

Why You Should Always Use RETURNING

Unless you’re bulk-loading millions of rows and the overhead of returning data is a genuine bottleneck (which it rarely is), you should make RETURNING your default. Here’s why:

  1. It’s More Efficient: One round trip to the database is always faster than two. Always.
  2. It’s Safer: It eliminates an entire class of race conditions and logic errors that come from trying to SELECT data after you’ve modified it. The data you get back is guaranteed to be the data that was written.
  3. It’s More Informative: Especially with ON CONFLICT, it’s the only way to know the final state of the row and, with a little system column magic, what operation actually occurred.

Stop guessing what your database just did. Tell it to hand over the evidence. Use RETURNING.