An identity column gets its value from the database (Chapter 4). After the INSERT, you often need that value.
A table reviews has 3 rows. Its id is an identity column. RETURNING gives the new id back in the same statement:
sql
INSERT INTO reviews (product_id, rating)
VALUES (1, 5)
RETURNING id;text
id
----
4INSERT … / UPDATE … / DELETE …the changeRETURNING idreturn these columns of the changed rows
After RETURNING, write a column list, the same as after SELECT. RETURNING * returns every column. The rows show in the Result tab.
Which rows come back
| Statement | RETURNING shows |
|---|---|
INSERT | each new row, with the values the database filled in (identity, DEFAULT) |
UPDATE | each changed row, after the change |
DELETE | each removed row, as it was before it was removed |
sql
DELETE FROM reviews
WHERE rating = 1
RETURNING *;reviews
| id | product_id | rating |
|---|---|---|
| 1 | 1 | 5 |
| 2 | 9 | 1 |
| 3 | 4 | 4 |
After a DELETE, the rows are gone from the table. RETURNING is the last time you can see them.