RETURNING in PostgreSQL

The RETURNING keyword in PostgreSQL gives you an opportunity to return, from the insert or update statement, the values of any columns after the insert or update was run. I mentioned this in passing in a few of my talks that touch on PostgreSQL recently, and it often gets Twitter comments, so here's a quick example of the RETURNING keyword in PostgreSQL. The newest releases of PostgreSQL are excellent, and I'm seeing many teams considering moving their traditional MySQL setups over — this is just one of the extra goodies that you get when you use PostgreSQL! Let's look at an example.

Tables With Calculated Fields

This feature is most useful when the insert or update statement will create fields in addition to the ones you insert. These might be created by having functions, triggers, or other fun things which will come together to create the eventual data for a row. For example, here's a simple table that has both an autoincrementing id field and a timestamp field with a default value:

CREATE TABLE items (
  id SERIAL primary key,
  name varchar,
  created timestamp with time zone default now()
);

When I insert an item into the table, I only need to supply the name and PostgreSQL will set the id and created fields. By using the RETURNING keyword on the end of my insert query, I can have PostgreSQL return those new values to me as part of the same operation. This is very handy indeed if you want to then go on and do something with this information (such as record the newly-inserted id value).

Here's the insert query with RETURNING in it:

INSERT INTO items (name) values ('bear') RETURNING id, created; 

This returns something like:

 id | created ----+------------------------------- 
  1 | 2016-11-17 08:47:20.493545+00 

Whether it's a running total, a UUID field, or some other calculated value, being able to access it as part of the query is pretty neat. It's a small thing, but one of my favorite features in PostgreSQL just for making the process a little bit more delightful as you go along.