This is a little tip I learned today from a colleague, and it's something so simple and so effective that I still wonder why it never popped in my radar for so many years.
But essentially the RETURNING is a clause you can use after an INSERT/UPDATE/DELETE statement, to simply return the data from the previous query.
For example, in an INSERT statement, you can do the following:
INSERT INTO customers (name, email)
VALUES ('John Doe', 'johndoe@email.com')
RETURNING customer_id;
This query would return the customer_id value of the newly inserted row.
True that if you use some libraries, they will do that for you, so maybe the ID of a row it could be just there with the response, like it does with the mysql2 npm package in Node.js, BUT, if you need to know other information, like something else that is autogenerated, you can use the RETURNING clause to get that on your SQL response.
You could also return the entire row by using RETURNING *, for example:
INSERT INTO customers (name, email)
VALUES ('John Doe', 'johndoe@email.com')
RETURNING *;
Will return the entire data of the row you just inserted.
And of course it will work* the same way for UPDATE/DELETE, for example:
-- Update
UPDATE customers
SET email = 'newemail@email.com'
WHERE customer_id = 10
RETURNING name, email;
-- Delete
DELETE FROM customers
WHERE customer_id = 10
RETURNING name, email;
* On PostgreSQL, but not MariaDB/MySQL
Update 25/10/2023
It turns out that while the RETURNING clause works just fine with PostgreSQL, MySQL/MariaDB have only partial support for it.
It still works for the INSERT and DELETE statements, but not for the UPDATE.
It does work for the REPLACE statement though, as described on the MariaDB docs.
Top comments (2)
It is a very useful thing if you are querying a database from an external source, for example from a client application, and you want to know what data was actually inserted. Especially if the SQL table generates and modifies some data automatically.
Omg! I had never heard of this, thanks!