Using RETURNING to Get Data After Insert/Update

Today we’re adding another magic trick to our toolkit: the RETURNING command. This is a powerful tool that lets you instantly get back the data you need after you insert, update, or delete something. Imagine you insert a row into a table and immediately get its unique ID back. Handy, right? Of course! Let’s dive in!

After you run INSERT, UPDATE, or DELETE, sometimes you need to instantly get the data that was affected. For example, after adding a new row to a table, you might want to know its automatically created primary key (unique ID). Instead of sending a separate query to get this info, you can do it right away with RETURNING as part of your operation.

Let’s say you’re adding a new student to the students table. Every student has a unique ID (let’s call it student_id) that’s generated automatically. After adding the new record, you want to use this ID right away to add course enrollments for the student. Without RETURNING, you’d have to first add the student, then run a separate SELECT query to find their ID. With RETURNING, you do it all in one go!

Syntax for the RETURNING Command

The RETURNING command goes at the end of your INSERT, UPDATE, or DELETE statements. Here’s the general syntax:

-- INSERT syntax:
INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING column;

-- UPDATE syntax:
UPDATE table
SET column1 = value1
WHERE condition
RETURNING column;

-- DELETE syntax:
DELETE FROM table
WHERE condition
RETURNING column;

RETURNING lets you specify which columns from the changed rows you want to get back. It can be just one column (like the primary key) or several columns at once.

Using RETURNING with INSERT

Let’s look at an example with the students table:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE
);

Let’s add a new student and instantly get their ID:

INSERT INTO students (first_name, last_name, email)
VALUES ('Otto', 'Lin', 'otto.lin@example.com')
RETURNING student_id;

This query will return the ID of the student you just added:

You just added Otto Lin as a student, and PostgreSQL instantly told you his student_id. Pretty sweet, right?

Now let’s insert and get back several columns at once. If you want not just student_id but also the student’s first and last name, you can ask for them with RETURNING:

INSERT INTO students (first_name, last_name, email)
VALUES ('Alex', 'Ming', 'alex.ming@example.com')
RETURNING student_id, first_name, last_name;

Result:

Using RETURNING with UPDATE

Sometimes after updating data, you want to know exactly which rows were changed. RETURNING is perfect for this.

Imagine student Otto Lin decided to change his email. Let’s update his info and instantly get the changed row back:

UPDATE students
SET email = 'lin.new@example.com'
WHERE student_id = 1
RETURNING student_id, email;

Result:

Now you know for sure that the email for the student with student_id = 1 was updated successfully.

Using RETURNING with DELETE

When you delete rows from a table, it’s sometimes useful to know which rows were deleted. With RETURNING, you can get the data from the deleted rows.

Here’s an example. Let’s say student Peter Ming was expelled. We’ll delete him from the table and get info about the deleted row:

DELETE FROM students
WHERE student_id = 2
RETURNING student_id, first_name, last_name;

Result:

Now you’re sure that it was Peter Ming who got deleted.

Practical Tips

Use RETURNING whenever you need data from the affected rows. This helps you avoid extra queries to your database.

Keep the returned data to a minimum. If you only need the primary key, just ask for that.

Combined with transactions, RETURNING is super powerful. For example, you can add data to several related tables, passing IDs around using RETURNING.

Common Mistakes When Using RETURNING

Newbies often forget that RETURNING only works with rows that were actually changed. For example, if your UPDATE or DELETE query didn’t find any rows, RETURNING will give you an empty result. Don’t freak out, that’s normal. Just make sure your query is correct, or handle this case in your app code.

Another common mistake: trying to use RETURNING for columns that don’t exist. Always double-check that all the columns you’re asking for actually exist in your table.

Now you know how RETURNING can make working with data way easier. This is one of those tools you’ll really appreciate when you start building real apps. Go crush it!