Thread: Return key from query

Hi everyone,

When adding a new record, we run an insert query which auto-increments
the primary key for the table. However the method (in java) which calls
this query must return the newly created key.

Any ideas on how to do this, preferably using a single transaction?

Thanks

On 11/02/2010 01:43 PM, Jonathan Tripathy wrote:
> Hi everyone,
>
> When adding a new record, we run an insert query which auto-increments
> the primary key for the table. However the method (in java) which
> calls this query must return the newly created key.
>
> Any ideas on how to do this, preferably using a single transaction?
>
> Thanks
>
Use "returning":

create table foo (bar serial, baz text);

insert into foo (baz) values ('test') returning bar;
  bar
-----
    1
(1 row)


Cheers,
Steve

On 11/02/2010 02:43 PM, Jonathan Tripathy wrote:
> Hi everyone,
>
> When adding a new record, we run an insert query which auto-increments
> the primary key for the table. However the method (in java) which calls
> this query must return the newly created key.
>
> Any ideas on how to do this, preferably using a single transaction?
>
> Thanks
>

Ah yes have your cake and eat it too.

If you app code (java) is making new instances and wants to have an id,
then the table cannot have an auto-id.

I'm big on java/class id generation (usually use UUID) but you do have
to take complete responsibility for it.  The hibernate folks frown on it
but their retort is imho woeful.  If you're not using uuid's you will
need a generator.  You can back it up with a persistent store so you
know where you left off, but you will want to (auto-)increment that id
table with a large value (say 5000) and have you generate dole them out
as needed at the app level.  When it has spent 5000 ids, it will go to
the server are ask for another 5000 (separate tx).  Please don't get
hung up on loosing some portion of the 5000 id when you restart or whatever.

On 11/03/2010 02:08 AM, Szymon Guz wrote:
>
>
> On 3 November 2010 00:41, Rob Sargent <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> wrote:
>
>
>
>     On 11/02/2010 03:03 PM, Szymon Guz wrote:
>     >
>     >
>     > On 2 November 2010 21:59, Rob Sargent <robjsargent@gmail.com
>     <mailto:robjsargent@gmail.com>
>     > <mailto:robjsargent@gmail.com <mailto:robjsargent@gmail.com>>> wrote:
>     >
>     >
>     >
>     >     On 11/02/2010 02:43 PM, Jonathan Tripathy wrote:
>     >     > Hi everyone,
>     >     >
>     >     > When adding a new record, we run an insert query which
>     auto-increments
>     >     > the primary key for the table. However the method (in java)
>     which
>     >     calls
>     >     > this query must return the newly created key.
>     >     >
>     >     > Any ideas on how to do this, preferably using a single
>     transaction?
>     >     >
>     >     > Thanks
>     >     >
>     >
>     >     Ah yes have your cake and eat it too.
>     >
>     >     If you app code (java) is making new instances and wants to
>     have an id,
>     >     then the table cannot have an auto-id.
>     >
>     >
>     > why?
>
>     Well I admit you could have a set-up wherein you retrieve an id-only
>     record from the db as part of the java constuctor but keeping that tx
>     open while the app decides whether or not it will "save" the record seem
>     a nightmare to me
>
>
> Sorry, I don't get it. I usually have an application that knows if it
> wants to write some data to database, or not. So it writes the data, and
> just gets from database the id that was set by database. No need of
> getting the id earlier in a transaction, although the simple insert that
> saves the data runs in a transaction of course.
> Another approach could be just getting the id from database, and saving
> the data using that id. If someone puts there any complicated logic
> between getting id and saving data, it is just a very bad software
> design, that has nothing common with the id/uuid problem.
>


If the client application wants to generate new instances and manipulate
them in lists and so forth, having the id before the instances actually
persist is really handy.  I'm of the opinion that the application makes
the instances (completely, with ids and all) not the database.


>
>     >
>     >
>     >     I'm big on java/class id generation (usually use UUID) but you
>     do have
>     >     to take complete responsibility for it.  The hibernate folks
>     frown on it
>     >     but their retort is imho woeful.  If you're not using uuid's
>     you will
>     >     need a generator.  You can back it up with a persistent store
>     so you
>     >     know where you left off, but you will want to (auto-)increment
>     that id
>     >     table with a large value (say 5000) and have you generate dole
>     them out
>     >     as needed at the app level.  When it has spent 5000 ids, it
>     will go to
>     >     the server are ask for another 5000 (separate tx).  Please
>     don't get
>     >     hung up on loosing some portion of the 5000 id when you restart or
>     >     whatever.
>     >
>     >
>     > I'm not fan of UUID, though I have to use it in many projects...
>     nothing
>     > special, just another id, not better, not worse than id, maybe except
>     > for the chance of collisions :)
>     >
>     http://en.wikipedia.org/wiki/Universally_unique_identifier#Random_UUID_probability_of_duplicates
>
>
> Yes... indeed, that all depends on the method of generating UUID, and
> the number of concurrent UUID numbers you want to generate. In the
> systems that I use, sometimes there is a really big number of new data
> per second, duplicates can occur, but I could just throw away the data
> with duplicated ids, something that I cannot do e.g. in a bank system.
>
>
>     I'll take those odds over a monotonically increasing id with concomitant
>     index rebuilds.
>
>
> If you have index on the UUID field (which of course you have, I
> suppose), and the index is sorted in the file, than you have a really
> great bloat in the index file. I will have to check that out how big,
> but I suppose UUIDs could be much slower due to that.
>
> Could someone enlighten me if this occurs?

It's only two words wide on a 64bit machine.
>
>
>
> regards
> Szymon


PS. I think you may have sent your reply to me only.

>>
>> Sorry, I don't get it. I usually have an application that knows if it
>> wants to write some data to database, or not. So it writes the data, and
>> just gets from database the id that was set by database. No need of
>> getting the id earlier in a transaction, although the simple insert that
>> saves the data runs in a transaction of course.
>> Another approach could be just getting the id from database, and saving
>> the data using that id. If someone puts there any complicated logic
>> between getting id and saving data, it is just a very bad software
>> design, that has nothing common with the id/uuid problem.
>>

All my software is doing is running a simple INSERT query on a table,
with the primary key auto-incremented. I just have no way of knowing
what the new ID is once the query is done. My problem is simpler than
soft folk here think, however I feer that the solution is harder than I
think :(

> -----Original Message-----
> From: Jonathan Tripathy [mailto:jonnyt@abpni.co.uk]
> Sent: Wednesday, November 03, 2010 12:46 PM
> To: Rob Sargent; pgsql-general@postgresql.org
> Subject: Re: Return key from query
>
>
> >>
> >> Sorry, I don't get it. I usually have an application that
> knows if it
> >> wants to write some data to database, or not. So it writes
> the data,
> >> and just gets from database the id that was set by
> database. No need
> >> of getting the id earlier in a transaction, although the simple
> >> insert that saves the data runs in a transaction of course.
> >> Another approach could be just getting the id from database, and
> >> saving the data using that id. If someone puts there any
> complicated
> >> logic between getting id and saving data, it is just a very bad
> >> software design, that has nothing common with the id/uuid problem.
> >>
>
> All my software is doing is running a simple INSERT query on
> a table, with the primary key auto-incremented. I just have
> no way of knowing what the new ID is once the query is done.
> My problem is simpler than soft folk here think, however I
> feer that the solution is harder than I think :(
>

No, it's not hard at all.
You were already given a solution: INSERT with "RETURNING" clause.
Check PG documentation regarding this clause.

Regards,
Igor Neyman