The RETURNING Keyword in PostgreSQL
RETURNING keyword in PostgreSQL gives 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 comment 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 favourite features in PostgreSQL just for making the process a little bit more delightful as you go along :)
That’s really nice!
As far as you know, am I correct in saying Postgres doesn’t have virtual columns? This would be a handy feature to combine with them.
Great! especially since I noticed you can use it in combination with INTO.
So you write a(n insert) function, need to check an optional (DEFAULT NULL) variable and you RETURNING INTO myvar and use that.
Made me happy when trying it – that should work, shouldn’t it – and it did.
Hi,
In my data setup, I am getting more than one row from update query, and getting an error in my POSTGRES code (query returned more than one row). Simply, I need a replacement of [RETURNING Column_name BULK COLLECT INTO array_variable_name;]