Over a million developers have joined DZone.

RETURNING in PostgreSQL

Here's a quick overview about using the RETURNING keyword in PostgreSQL, letting you return values from after you run your insert or update statements.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:
postgresql ,keyword ,database ,returning

Published at DZone with permission of Lorna Mitchell, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}