How to Use RETURNING Clause in PostgreSQL: Syntax and Examples

In PostgreSQL, the RETURNING clause lets you get the affected rows from INSERT, UPDATE, or DELETE statements immediately. You add RETURNING followed by the columns you want to retrieve, and PostgreSQL returns those rows as query results.

Syntax

The RETURNING clause is added at the end of INSERT, UPDATE, or DELETE statements. It specifies which columns to return from the affected rows.

• INSERT: Returns columns of the newly inserted rows.
• UPDATE: Returns columns of the updated rows.
• DELETE: Returns columns of the deleted rows.

sql

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

UPDATE table_name SET column1 = value WHERE condition RETURNING column1, column2;

DELETE FROM table_name WHERE condition RETURNING column1, column2;

Example

This example shows how to insert a new user and get the generated id and username immediately using RETURNING.

sql

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

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

Output

 id | username 
----+----------
  1 | alice
(1 row)

Common Pitfalls

Some common mistakes when using RETURNING include:

• Forgetting to specify columns after RETURNING, which returns all columns and may be inefficient.
• Using RETURNING in statements that do not support it (like SELECT).
• Expecting RETURNING to work in older PostgreSQL versions before 8.2.

sql

/* Wrong: No columns specified, returns all columns which might be large */
INSERT INTO users (username, email) VALUES ('bob', '[e-mail]') RETURNING *;

/* Right: Specify columns explicitly */
INSERT INTO users (username, email) VALUES ('bob', '[e-mail]') RETURNING id, username;

Quick Reference

Clause	Description	Example
INSERT ... RETURNING	Returns columns of inserted rows	INSERT INTO t (a) VALUES (1) RETURNING id;
UPDATE ... RETURNING	Returns columns of updated rows	UPDATE t SET a=2 WHERE id=1 RETURNING a;
DELETE ... RETURNING	Returns columns of deleted rows	DELETE FROM t WHERE id=1 RETURNING id;

Key Takeaways

Use RETURNING to get affected rows immediately after INSERT, UPDATE, or DELETE.

Specify only needed columns after RETURNING for better performance.

RETURNING is supported in PostgreSQL 8.2 and later.

It helps avoid extra SELECT queries to fetch changed data.

RETURNING does not work with SELECT statements.