Thread: Example of RETURNING clause to get auto-generated keys from INSERT
Greetings, I am looking into possibly contributing some code for one of the existing PG drivers, that will allow us to, after INSERT, get a ResultSet containing the server generated keys (sequences or other). I've been told that (short of implementing a new V4 server protocol) the most effective way to do this, may be to use PG's RETURNING clause. However I could really use some example queries, since I'm not proficient enough with PG and this clause to know how to get the values. I do know that the query should: -support multiple values, ie. insert int tbl (a,b) values (1,2),(3,4), should return a result with 2 rows containing the new keys (one for each column the users declares). -query the values atomically (so that insert by another client won't skew the curval / sequence) (obvious but deserves mention) -ideally be predictable - just in case the sequence doesn't use a increment value of one, or if some other non-sequence (triggers) or numeric (uuids) generator is used. -ideally not require parsing the user INSERT query (for table names etc), though I expect that (in order to use RETURNING) I will have to append to it. The API I'd implement this for (jdbc), does require us to declare what columns we are interested in getting generated keys for, so that might preclude needing resultset metadata to know which columns have server generated keys. So if anyone can give SQL samples of how to best make this work, I would be very much appreciative. Thanks, Ken
Ken Johanson wrote:
> -support multiple values, ie. insert int tbl (a,b) values (1,2),(3,4),
> should return a result with 2 rows containing the new keys (one for each
> column the users declares).
> -query the values atomically (so that insert by another client won't
> skew the curval / sequence) (obvious but deserves mention)
> -ideally be predictable - just in case the sequence doesn't use a
> increment value of one, or if some other non-sequence (triggers) or
> numeric (uuids) generator is used.
> -ideally not require parsing the user INSERT query (for table names
> etc), though I expect that (in order to use RETURNING) I will have to
> append to it.
This sample does most of what you want:
alvherre=# create table bar (a serial, b text);
NOTICE: CREATE TABLE will create implicit sequence "bar_a_seq" for serial column "bar.a"
CREATE TABLE
alvherre=# insert into bar (b) values ('hello'), ('world') returning a;
a
---
1
2
(2 filas)
INSERT 0 2
As you predicted, you need to know the column names of the key, which
you can obtain by peeking the system catalogs. That is, unless you use
a "returning *", but then it'll give you all columns and you'll have to
figure out which ones are part of the key anyway. Of course, with
multiple column keys it gets a bit more complex, but it's not really
rocket science.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Let's say you have a table with "id, value" columns.
And your normal query would be this:
INSERT into mytable (id,value) values (1,"foo"),(2,"bar");
Your new query would be like this:
INSERT into mytable (id,value) values (1,"foo"),(2,"bar")
RETURNING id;
And you would get a result back with one column (id) and
two rows (the newly inserted keys). You can also return
other fields if you like, you're not limited to just the
generated keys.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ken Johanson
Sent: Tuesday, January 23, 2007 10:50 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Example of RETURNING clause to get auto-generated
keys from INSERT
Greetings,
I am looking into possibly contributing some code for one of the
existing PG drivers, that will allow us to, after INSERT, get a
ResultSet containing the server generated keys (sequences or other).
I've been told that (short of implementing a new V4 server protocol) the
most effective way to do this, may be to use PG's RETURNING clause.
However I could really use some example queries, since I'm not
proficient enough with PG and this clause to know how to get the values.
I do know that the query should:
-support multiple values, ie. insert int tbl (a,b) values (1,2),(3,4),
should return a result with 2 rows containing the new keys (one for each
column the users declares).
-query the values atomically (so that insert by another client won't
skew the curval / sequence) (obvious but deserves mention)
-ideally be predictable - just in case the sequence doesn't use a
increment value of one, or if some other non-sequence (triggers) or
numeric (uuids) generator is used.
-ideally not require parsing the user INSERT query (for table names
etc), though I expect that (in order to use RETURNING) I will have to
append to it.
The API I'd implement this for (jdbc), does require us to declare what
columns we are interested in getting generated keys for, so that might
preclude needing resultset metadata to know which columns have server
generated keys.
So if anyone can give SQL samples of how to best make this work, I would
be very much appreciative.
Thanks,
Ken
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
On Tue, Jan 23, 2007 at 23:19:47 -0600, Adam Rich <adam.r@sbcglobal.net> wrote: > > And your normal query would be this: > > INSERT into mytable (id,value) values (1,"foo"),(2,"bar"); > > Your new query would be like this: > > INSERT into mytable (id,value) values (1,"foo"),(2,"bar") > RETURNING id; Note that you will want to be using single quotes not double quotes.
Adam Rich wrote: > Let's say you have a table with "id, value" columns. > > And your normal query would be this: > > INSERT into mytable (id,value) values (1,"foo"),(2,"bar"); > > Your new query would be like this: > > INSERT into mytable (id,value) values (1,"foo"),(2,"bar") > RETURNING id; > > And you would get a result back with one column (id) and > two rows (the newly inserted keys). You can also return > other fields if you like, you're not limited to just the > generated keys. > Thank you Alvaro and Adam, Now playing devil's advocate, can anyone see scenarios where this will not work as expected? Examples (descriptions not sql necessarily) of those would be helpful too... Ken
> > Now playing devil's advocate, can anyone see scenarios where this will > not work as expected? Examples (descriptions not sql necessarily) of > those would be helpful too... > Just to be sure, will the RETURNING clause work with custom sequences (say, non numeric or increment by two) or other types of key generators?... And how will triggers interfere with it (if at all)? I honestly have limited experience with server generated keys that are not numeric/serial (or uuids), or with cases where triggers, constraints, etc might come into play (I'm used to using the DB mostly as a storage device and using server-side logic..) Ken
Ken Johanson wrote: > Just to be sure, will the RETURNING clause work with custom > sequences (say, non numeric or increment by two) or other types of > key generators?... And how will triggers interfere with it (if at > all)? RETURNING has nothing to do with sequences per se - it's just a way of getting at any of the columns of the new row, regardless of how they got filled. > The optional RETURNING clause causes INSERT to compute and return > value(s) based on each row actually inserted. This is primarily > useful for obtaining values that were supplied by defaults, such as > a serial sequence number. However, any expression using the table's > columns is allowed. The syntax of the RETURNING list is identical > to that of the output list of SELECT. http://www.postgresql.org/docs/8.2/interactive/sql-insert.html - John Burger MITRE
Ken Johanson wrote: > > > >Now playing devil's advocate, can anyone see scenarios where this will > >not work as expected? Examples (descriptions not sql necessarily) of > >those would be helpful too... > > > > Just to be sure, will the RETURNING clause work with custom sequences > (say, non numeric or increment by two) or other types of key > generators?... And how will triggers interfere with it (if at all)? > > I honestly have limited experience with server generated keys that are > not numeric/serial (or uuids), or with cases where triggers, > constraints, etc might come into play (I'm used to using the DB mostly > as a storage device and using server-side logic..) As far as I know, RETURNING will give you exactly the values that are put into the table. If you had a weird sequence or strange stuff invoked in functions, they will be computed much earlier than the RETURNING values be fetched, so the latter will get the correct values all the time. (It would be quite dumb to do otherwise anyway). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Ken Johanson wrote:
>> Just to be sure, will the RETURNING clause work with custom sequences
>> (say, non numeric or increment by two) or other types of key
>> generators?... And how will triggers interfere with it (if at all)?
> As far as I know, RETURNING will give you exactly the values that are
> put into the table.
RETURNING evaluates the given expression-list over the values that were
actually stored. There's no way for a datatype or BEFORE trigger to
"fool" it. The only possibly interesting case is if you had an AFTER
trigger that proceeded to modify the stored row by issuing an UPDATE
... but that would be a pretty silly/inefficient way to do things,
and even then I think that RETURNING is telling the truth as of the time
that the tuple insert/update happened. It can't be supposed to be
prescient about subsequent changes.
regards, tom lane