Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

RETURNING in PostgreSQL

DZone's Guide to

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 ·
Free Resource

Built by the engineers behind Netezza and the technology behind Amazon Redshift, AnzoGraph is a native, Massively Parallel Processing (MPP) distributed Graph OLAP (GOLAP) database that executes queries more than 100x faster than other vendors.  

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.

Download AnzoGraph now and find out for yourself why it is acknowledged as the most complete all-in-one data warehouse for BI style and graph analytics.  

Topics:
postgresql ,keyword ,database ,returning

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}