INSERT with RETURNING clause in PostgreSQL - Deep Dive

Overview - INSERT with RETURNING clause

What is it?

The INSERT statement adds new rows to a table in a database. The RETURNING clause lets you get back data from the rows you just inserted, like their new IDs or other column values. This means you can insert data and immediately see details about what was added without running a separate query. It makes working with new data faster and simpler.

Why it matters

Without the RETURNING clause, after inserting data, you would need to run another query to find out details like the new row's ID. This extra step can slow down your program and make your code more complex. RETURNING solves this by giving you the inserted data right away, saving time and reducing mistakes. It helps keep your database interactions efficient and clean.

Where it fits

Before learning this, you should understand basic SQL INSERT statements and how tables store data. After mastering RETURNING, you can explore advanced data manipulation techniques like UPSERT (INSERT ON CONFLICT) and using RETURNING with other commands like UPDATE or DELETE.

Mental Model

Core Idea

INSERT with RETURNING lets you add data and instantly get back details about what you added in one step.

Think of it like...

It's like ordering food at a restaurant and the waiter immediately brings you the receipt with your order number, so you know exactly what you ordered without asking again.

┌───────────────┐       ┌───────────────┐
│   INSERT      │──────▶│  New row data │
│  statement    │       │  returned by  │
│  adds rows    │       │  RETURNING    │
└───────────────┘       └───────────────┘

Build-Up - 6 Steps

1

Foundation

Basic INSERT statement usage

Concept: Learn how to add new rows to a table using INSERT.

The INSERT statement adds new data to a table. For example, to add a user to a users table: INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]'); This adds one new row with the given name and email.

Result

A new row with name 'Alice' and email '[e-mail]' is added to the users table.

Understanding how to add data is the first step before learning how to get information about what you added.

2

Foundation

Retrieving data after INSERT without RETURNING

Concept: See how to get inserted row details without RETURNING by using a separate query.

After inserting a row, you might want to know its ID. Without RETURNING, you insert first, then query: INSERT INTO users (name, email) VALUES ('Bob', '[e-mail]'); SELECT id FROM users WHERE email = '[e-mail]'; This requires two steps and extra code.

Result

You get the new user's ID by running a second query after insertion.

This shows why RETURNING is useful: it avoids extra queries and keeps code simpler.

3

Intermediate

Using RETURNING to get inserted row data

🤔Before reading on: do you think RETURNING can return multiple columns or just the new row's ID? Commit to your answer.

Concept: RETURNING lets you get back any columns from the inserted rows immediately.

You can add RETURNING after INSERT to get back columns: INSERT INTO users (name, email) VALUES ('Carol', '[e-mail]') RETURNING id, name; This returns the new row's id and name in one step.

Result

The query returns a result set with the id and name of the inserted row, e.g., (5, 'Carol').

Knowing RETURNING returns any columns lets you get exactly the data you need without extra queries.

4

Intermediate

RETURNING with multiple rows inserted

🤔Before reading on: if you insert 3 rows with RETURNING, do you get back 1 row or 3 rows? Commit to your answer.

Concept: RETURNING returns data for every row inserted, not just one.

When inserting multiple rows: INSERT INTO users (name, email) VALUES ('Dave', '[e-mail]'), ('Eve', '[e-mail]'), ('Frank', '[e-mail]') RETURNING id, email; You get back a result with one row per inserted user.

Result

The query returns multiple rows, each with the id and email of the inserted users.

RETURNING scales naturally to batch inserts, giving you full feedback on all new rows.

5

Advanced

Using RETURNING with INSERT ... ON CONFLICT

🤔Before reading on: do you think RETURNING works with INSERT statements that handle conflicts? Commit to your answer.

Concept: RETURNING works with UPSERT operations to show inserted or updated rows.

You can combine RETURNING with ON CONFLICT to insert or update: INSERT INTO users (email, name) VALUES ('[e-mail]', 'Frank') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name RETURNING id, name; This returns the id and name whether the row was inserted or updated.

Result

You get back the id and name of the affected row, no matter if it was new or updated.

RETURNING helps track changes even in complex insert-or-update logic, simplifying data handling.

6

Expert

Performance and transactional behavior of RETURNING

🤔Before reading on: does RETURNING add significant overhead or change transaction behavior? Commit to your answer.

Concept: RETURNING is efficient and works inside transactions without extra cost or side effects.

RETURNING is implemented inside the database engine to return data as rows are inserted. It does not require extra queries or locks beyond the insert itself. It works seamlessly inside transactions, so you can insert and get data atomically. This means you can rely on RETURNING for safe, fast feedback in production systems.

Result

RETURNING returns inserted data immediately with minimal performance impact and full transactional safety.

Understanding RETURNING's efficiency and atomicity helps you trust it in high-performance, concurrent applications.

Under the Hood

When you run INSERT with RETURNING, PostgreSQL executes the insert operation and simultaneously collects the specified columns from the new rows. Internally, it streams these rows back to the client as part of the same command execution. This avoids running a separate SELECT query and reduces round trips between client and server.

Why designed this way?

RETURNING was designed to simplify common patterns where applications need to know details about inserted rows, like auto-generated IDs. Before RETURNING, developers had to run extra queries, which was inefficient and error-prone. By integrating data retrieval into the insert command, PostgreSQL improves performance and developer experience.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│  Client sends │──────▶│ PostgreSQL     │──────▶│  Insert rows  │
│  INSERT +     │       │  engine       │       │  into table   │
│  RETURNING    │       │               │       └───────────────┘
└───────────────┘       │               │               │
                        │ Collects data │               ▼
                        │ from inserted │       ┌───────────────┐
                        │ rows          │──────▶│ Returns data  │
                        └───────────────┘       │ to client    │
                                                └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does RETURNING only work for single-row inserts? Commit to yes or no.

Common Belief: RETURNING only works when inserting one row at a time.

Reality: RETURNING works with multi-row inserts and returns one result row per inserted row.

Why it matters: Believing this limits your use of RETURNING and may cause you to write inefficient code with multiple single inserts.

Quick: Does RETURNING cause the insert to be slower because it does extra work? Commit to yes or no.

Common Belief: RETURNING significantly slows down inserts because it adds extra processing.

Reality: RETURNING is optimized inside PostgreSQL and adds minimal overhead compared to running a separate SELECT after insert.

Why it matters: Avoiding RETURNING due to performance fears can lead to more complex and slower code overall.

Quick: Can RETURNING be used with UPDATE or DELETE statements? Commit to yes or no.

Common Belief: RETURNING only works with INSERT statements.

Reality: RETURNING also works with UPDATE and DELETE to return affected rows.

Why it matters: Not knowing this misses opportunities to simplify data retrieval in other data modification commands.

Quick: Does RETURNING guarantee the order of returned rows matches the insert order? Commit to yes or no.

Common Belief: RETURNING always returns rows in the exact order they were inserted.

Reality: PostgreSQL does not guarantee order of rows returned by RETURNING unless you add ORDER BY in a subquery.

Why it matters: Assuming order can cause bugs when processing returned data if order matters.

Expert Zone

1

RETURNING can return computed columns or expressions, not just table columns, allowing flexible data retrieval.

2

Using RETURNING inside CTEs (WITH clauses) enables chaining multiple data modifications with immediate feedback.

3

RETURNING output can be consumed directly by client applications or used in server-side functions for complex workflows.

When NOT to use

Avoid RETURNING when inserting huge batches where returning all rows would consume excessive memory or network bandwidth. Instead, consider returning only keys or using separate queries with pagination.

Production Patterns

In real systems, RETURNING is used to get auto-generated IDs for new rows, confirm data changes in UPSERT operations, and feed data pipelines without extra queries. It's common in APIs to return created resource details immediately.

Connections

Transactions

RETURNING works inside transactions to provide atomic data feedback.

Understanding transactions helps you see how RETURNING ensures you get consistent data even if multiple changes happen together.

REST API design

RETURNING supports REST APIs by returning created resource details in one step.

Knowing RETURNING helps backend developers build efficient APIs that respond with new resource info immediately after creation.

Functional programming

RETURNING embodies the idea of functions returning values immediately after execution.

Seeing RETURNING as a function returning output clarifies its role in SQL as more than just a command but a data-producing operation.

Common Pitfalls

#1 Expecting RETURNING to return data without specifying columns.

Wrong approach:

INSERT INTO users (name) VALUES ('Gina') RETURNING;

Correct approach:

INSERT INTO users (name) VALUES ('Gina') RETURNING id, name;

Root cause:

RETURNING requires explicit column names or *; omitting them causes syntax errors.

#2 Using RETURNING with INSERT but ignoring the returned data in application code.

Wrong approach:

INSERT INTO users (name) VALUES ('Hank') RETURNING id;

Correct approach:

id = INSERT INTO users (name) VALUES ('Hank') RETURNING id;

Root cause:

Not capturing RETURNING output wastes its benefit and may cause missed data like new IDs.

#3 Assuming RETURNING returns rows in insert order without ordering.

Wrong approach:

INSERT INTO users (name) VALUES ('Ivy'), ('Jack') RETURNING id, name;

Correct approach:

WITH ins AS (INSERT INTO users (name) VALUES ('Ivy'), ('Jack') RETURNING id, name) SELECT * FROM ins ORDER BY id;

Root cause:

PostgreSQL does not guarantee order in RETURNING results; explicit ordering is needed if order matters.

Key Takeaways

INSERT with RETURNING lets you add rows and get back their data in one simple step.

RETURNING works with single or multiple rows and can return any columns you specify.

It improves efficiency by avoiding extra queries and works well with UPSERT and transactions.

Understanding RETURNING helps you write cleaner, faster database code and build better applications.

Be careful to specify columns in RETURNING and handle its output properly to avoid common mistakes.