Return key from query

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 2 November 2010 21:43, Jonathan Tripathy <jonnyt@abpni.co.uk> 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

Try insert returning, something like this:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
RETURNING did;

http://www.postgresql.org/docs/9.0/static/sql-insert.html

regards
Szymon

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 2 November 2010 21:59, Rob Sargent <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?

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 :)

regards
Szymon

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 :(

On 3 November 2010 17:46, Jonathan Tripathy <jonnyt@abpni.co.uk> wrote:

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 :(

Hi,
why harder? Simple INSERT RETURNING doesn't work for you?

regards
Szymon

-----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