INSERT with RETURNING clause in PostgreSQL

Introduction

The INSERT with RETURNING clause lets you add new data to a table and immediately get back the inserted rows or specific columns. This saves extra steps.

When you add a new user and want to get their unique ID right away.

When inserting a new order and need to know the order number generated by the database.

When you want to confirm what data was actually saved after an insert.

When you want to insert data and use the returned values in your program without another query.

Syntax

PostgreSQL

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) RETURNING column1, column2, ...;

You can return all columns by using RETURNING *.

RETURNING clause works only in PostgreSQL and some other databases, not all SQL databases support it.

Examples

Insert a new employee and get back the generated id.

PostgreSQL

INSERT INTO employees (name, age) VALUES ('Alice', 30) RETURNING id;

Insert a product and return all columns of the new row.

PostgreSQL

INSERT INTO products (name, price) VALUES ('Book', 12.99) RETURNING *;

Insert an order and return only order_id and total columns.

PostgreSQL

INSERT INTO orders (customer_id, total) VALUES (5, 100.50) RETURNING order_id, total;

Sample Program

This creates a users table with an auto-increment id. Then it inserts one user and returns the id and username of the inserted row.

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, username TEXT, email TEXT);

INSERT INTO users (username, email) VALUES ('john_doe', '[e-mail]') RETURNING id, username;

Important Notes

RETURNING helps avoid running a separate SELECT after INSERT.

If you insert multiple rows, RETURNING returns all inserted rows.

Make sure your client or tool supports reading the returned rows.

Summary

INSERT with RETURNING adds data and immediately returns specified columns.

It is useful to get auto-generated IDs or confirm inserted data.

Works well in PostgreSQL to simplify data workflows.