SQL-question: returning the id of an insert querry

**Martijn van Oosterhout** · Nov 12 '05, 12:35 AM

Re: SQL-question: returning the id of an insert querry

After you've done the insert on the address table, you can use
currval('addres s_id_seq') (or equivalent) to get the ID. Ofcourse you have
to have used nextval() for the original insert.

Hope this helps,

On Sun, Nov 09, 2003 at 11:29:49AM +0100, Andreas Fromm wrote:[color=blue]
> Hi,
>
> Im building an user database with many tables keeping the data for the
> Address, Phone numbers, etc which are referenced by a table where I keep
> the single users. My question is, how do I get the "Id"-value of a newly
> inserted address to store it in the referencing user table:
>
> (a) INSERT INTO address VALUES (....);
>
> (b) INSERT INTO users VALUES ( name, ... , address , ... );
>
> where address should hold the value of the Id from the Adress table.
>
>
> Do have to do an
> SELECT id FROM address WHERE oid = oid_returned_by _insert(a)
> or something like that after doing the insert(a) to get the correct id
> value, or is there a better way to do this.
>
> Im writing my app in Perl with DBD/DBI
>
>
> Thanks in advance,
>
> Andreas Fromm
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/riFbY5Twig3Ge+Y RArk/AKDU1nU6pzTtFVY jWXwsV0Dd2VtYag CgiBm4
SCBl2fXmByxYY8+ wHZ965mQ=
=rQMo
-----END PGP SIGNATURE-----

**Andreas Fromm** · Nov 12 '05, 12:35 AM

Re: SQL-question: returning the id of an insert querry

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Martijn van Oosterhout wrote:[color=blue]
> After you've done the insert on the address table, you can use
> currval('addres s_id_seq') (or equivalent) to get the ID. Ofcourse you have
> to have used nextval() for the original insert.
>
> Hope this helps,
>[/color]
...going to try it. Thanks

Andreas Fromm

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/rkMwPkvkZVZzNY0 RAnajAJ0ePCTi/UODhGAxOs5NuptZ AT0tUgCgpNAz
Oqh8rM934O3SRRz v4Mh9S4I=
=E71z
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Scott Chapman** · Nov 12 '05, 12:35 AM

Re: SQL-question: returning the id of an insert querry

On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:[color=blue]
> After you've done the insert on the address table, you can use
> currval('addres s_id_seq') (or equivalent) to get the ID. Ofcourse you
> have to have used nextval() for the original insert.[/color]

What if someone else inserts another address before I get the currval?
I'm out of luck then, right?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Doug McNaught** · Nov 12 '05, 12:35 AM

Re: SQL-question: returning the id of an insert querry

Scott Chapman <scott_list@mis chko.com> writes:
[color=blue]
> On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:[color=green]
> > After you've done the insert on the address table, you can use
> > currval('addres s_id_seq') (or equivalent) to get the ID. Ofcourse you
> > have to have used nextval() for the original insert.[/color]
>
> What if someone else inserts another address before I get the currval?
> I'm out of luck then, right?[/color]

No, currval() handles that--see the docs.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Alvaro Herrera** · Nov 12 '05, 12:35 AM

Re: SQL-question: returning the id of an insert querry

On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:[color=blue]
> On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:[color=green]
> > After you've done the insert on the address table, you can use
> > currval('addres s_id_seq') (or equivalent) to get the ID. Ofcourse you
> > have to have used nextval() for the original insert.[/color]
>
> What if someone else inserts another address before I get the currval?
> I'm out of luck then, right?[/color]

No, currval is concurrency-safe. That's exactly what sequences are for.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Scott Chapman** · Nov 12 '05, 12:36 AM

Re: SQL-question: returning the id of an insert querry

On Sunday 09 November 2003 10:52, Alvaro Herrera wrote:[color=blue]
> On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:[color=green]
> > On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:[color=darkred]
> > > After you've done the insert on the address table, you can use
> > > currval('addres s_id_seq') (or equivalent) to get the ID. Ofcourse
> > > you have to have used nextval() for the original insert.[/color]
> >
> > What if someone else inserts another address before I get the
> > currval? I'm out of luck then, right?[/color]
>
> No, currval is concurrency-safe. That's exactly what sequences are
> for.[/color]

I just want to clarify what I mean here to make sure I understand this
right. I have a table, A, that has a ID field which defaults to nextval
of a sequence, SA.

Chronological events here:

X inserts a new record into A.
Y inserts a new record into A.
X fetches currval of the SA. What value does X get in this case, the one
from X's insert or Y's?

Scott

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

**Alvaro Herrera** · Nov 12 '05, 12:36 AM

Re: SQL-question: returning the id of an insert querry

On Mon, Nov 10, 2003 at 08:09:29AM -0800, Scott Chapman wrote:
[color=blue]
> Chronological events here:
>
> X inserts a new record into A.
> Y inserts a new record into A.
> X fetches currval of the SA. What value does X get in this case, the one
> from X's insert or Y's?[/color]

X's.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"¿Qué importan los años? Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

**David Green** · Nov 12 '05, 12:36 AM

Re: SQL-question: returning the id of an insert querry

Are X & Y two different connections?
If you execute 2 statements on the same connection and then get currval()
it will give the last generated id.

Ex.
On 1 connection:
INSERT INTO A (fld) VALUES (val); -- id generated = 1
INSERT INTO A (fld) VALUES (val2); -- id generated = 2
SELECT currval('SA');
2

On 2 connections:
conn1.execute(" INSERT INTO A (fld) VALUES (val)") -- id generated = 1
conn2.execute(" INSERT INTO A (fld) VALUES (val2)") -- id generated = 2
conn1.execute(" SELECT currval('SA')")
1
conn2.execute(" SELECT currval('SA')")
2

David Green
Sage Automation, Inc

-----Original Message-----
From: pgsql-general-owner@postgresq l.org
[mailto:pgsql-general-owner@postgresq l.org]On Behalf Of Scott Chapman
Sent: Monday, November 10, 2003 10:09 AM
To: Alvaro Herrera
Cc: Martijn van Oosterhout; Andreas Fromm; pgsql-general@postgre sql.org
Subject: Re: [GENERAL] SQL-question: returning the id of an insert
querry

On Sunday 09 November 2003 10:52, Alvaro Herrera wrote:[color=blue]
> On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:[color=green]
> > On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:[color=darkred]
> > > After you've done the insert on the address table, you can use
> > > currval('addres s_id_seq') (or equivalent) to get the ID. Ofcourse
> > > you have to have used nextval() for the original insert.[/color]
> >
> > What if someone else inserts another address before I get the
> > currval? I'm out of luck then, right?[/color]
>
> No, currval is concurrency-safe. That's exactly what sequences are
> for.[/color]

I just want to clarify what I mean here to make sure I understand this
right. I have a table, A, that has a ID field which defaults to nextval
of a sequence, SA.

Chronological events here:

X inserts a new record into A.
Y inserts a new record into A.
X fetches currval of the SA. What value does X get in this case, the one
from X's insert or Y's?

Scott

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Scott Chapman** · Nov 12 '05, 12:36 AM

Re: SQL-question: returning the id of an insert querry

On Monday 10 November 2003 08:23, David Green wrote:[color=blue]
> Are X & Y two different connections?
> If you execute 2 statements on the same connection and then get
> currval() it will give the last generated id.
>
> Ex.
> On 1 connection:
> INSERT INTO A (fld) VALUES (val); -- id generated = 1
> INSERT INTO A (fld) VALUES (val2); -- id generated = 2
> SELECT currval('SA');
> 2[/color]

Thanks for the clarification. With web applications and connection
pooling, it would appear that it's quite easy to get incorrect values
back. This is what I thought.

I talked with the author or SQLObject about this recently and I thnk
he's implementing this correctly, by querying the cursor for the last
OID?:

def _queryInsertID( self, conn, table, idName, names, values):
c = conn.cursor()
q = self._insertSQL (table, names, values)
if self.debug:
print 'QueryIns: %s' % q
c.execute(q)
c.execute('SELE CT %s FROM %s WHERE oid = %s'
% (idName, table, c.lastoid()))
return c.fetchone()[0]

The other way to do it would be to manually fetch nextval and insert
into the table over-riding the default for the ID field (assuming it
defaulted to the nextval in the sequence). I don't know which way is
best (for performance, for instance).

It's be nice if INSERT could be made to return the OID or (better yet)
the primary key field value when it completes. That would solve this
problem in one action and completely remove the need for the second
query. I expect it would have to be user-togglable so it didn't break
with existing code?

Scott

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Kathy Zhu** · Nov 12 '05, 12:36 AM

Re: SQL-question: returning the id of an insert querry

I saw this method of Statement class in jdbc.
Will the return int contain the autogenerated key value ??

public int executeUpdate(S tring sql,
int autoGeneratedKe ys)
throws SQLException

thanks,
kathy

Scott Chapman wrote:
[color=blue]
> On Monday 10 November 2003 08:23, David Green wrote:[color=green]
> > Are X & Y two different connections?
> > If you execute 2 statements on the same connection and then get
> > currval() it will give the last generated id.
> >
> > Ex.
> > On 1 connection:
> > INSERT INTO A (fld) VALUES (val); -- id generated = 1
> > INSERT INTO A (fld) VALUES (val2); -- id generated = 2
> > SELECT currval('SA');
> > 2[/color]
>
> Thanks for the clarification. With web applications and connection
> pooling, it would appear that it's quite easy to get incorrect values
> back. This is what I thought.
>
> I talked with the author or SQLObject about this recently and I thnk
> he's implementing this correctly, by querying the cursor for the last
> OID?:
>
> def _queryInsertID( self, conn, table, idName, names, values):
> c = conn.cursor()
> q = self._insertSQL (table, names, values)
> if self.debug:
> print 'QueryIns: %s' % q
> c.execute(q)
> c.execute('SELE CT %s FROM %s WHERE oid = %s'
> % (idName, table, c.lastoid()))
> return c.fetchone()[0]
>
> The other way to do it would be to manually fetch nextval and insert
> into the table over-riding the default for the ID field (assuming it
> defaulted to the nextval in the sequence). I don't know which way is
> best (for performance, for instance).
>
> It's be nice if INSERT could be made to return the OID or (better yet)
> the primary key field value when it completes. That would solve this
> problem in one action and completely remove the need for the second
> query. I expect it would have to be user-togglable so it didn't break
> with existing code?
>
> Scott
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org[/color]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

**Paul Thomas** · Nov 12 '05, 12:36 AM

Re: SQL-question: returning the id of an insert querry

On 10/11/2003 17:22 Kathy Zhu wrote:[color=blue]
> I saw this method of Statement class in jdbc.
> Will the return int contain the autogenerated key value ??
>
> public int executeUpdate(S tring sql,
> int autoGeneratedKe ys)
> throws SQLException
>
> thanks,
> kathy[/color]

This is one of a number of JDBC3 extensions which are just stub methods
ATM. You'll find it will just throw an exception if called.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Bruno Wolff III** · Nov 12 '05, 12:36 AM

Re: SQL-question: returning the id of an insert querry

On Mon, Nov 10, 2003 at 08:56:03 -0800,
Scott Chapman <scott_list@mis chko.com> wrote:[color=blue]
>
> Thanks for the clarification. With web applications and connection
> pooling, it would appear that it's quite easy to get incorrect values
> back. This is what I thought.[/color]

Not normally. Normally both queries are going to be done in one web
request and it would be very unusual to be using a system where
anyone else could use the same connection for a query in between.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Ian Harding** · Nov 12 '05, 12:37 AM

Re: SQL-question: returning the id of an insert querry

Scott Chapman wrote:
[color=blue]
>On Monday 10 November 2003 08:23, David Green wrote:
>
>[color=green]
>>Are X & Y two different connections?
>>If you execute 2 statements on the same connection and then get
>>currval() it will give the last generated id.
>>
>>Ex.
>>On 1 connection:
>>INSERT INTO A (fld) VALUES (val); -- id generated = 1
>>INSERT INTO A (fld) VALUES (val2); -- id generated = 2
>>SELECT currval('SA');
>>2
>>
>>[/color]
>
>Thanks for the clarification. With web applications and connection
>pooling, it would appear that it's quite easy to get incorrect values
>back. This is what I thought.
>[/color]
Huh? My web application has connection pooling and it goes like this:

<receive a request to do something>
Get a handle from the pool.
Do your insert.
Do your currval select.
Do whatever else you need to do...return data to user maybe.
Put the handle back in pool.
<wait for more requests to do something>

Nobody can grab my database handle til I am done with it. I can use it
as much as I like before I put it back. It is put back by default at
the end of the function if not explicitly put back.

You will never get "incorrect values" if you call currval immediately
after an insert while using the same handle.

I would not use a web application that got a new handle for every sql
statement executed.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Andrew Sullivan** · Nov 12 '05, 12:38 AM

Re: SQL-question: returning the id of an insert querry

On Mon, Nov 10, 2003 at 08:56:03AM -0800, Scott Chapman wrote:[color=blue]
> I talked with the author or SQLObject about this recently and I thnk
> he's implementing this correctly, by querying the cursor for the last
> OID?:[/color]

That won't scale unless you index oid. And your tables will all need
oids, which is not standard any more.

If you do your work in one transaction and get the currval that way,
it is impossible to go wrong. Also, if you don't return the
connection to the pool before getting the currval, you will not go
wrong.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@liberty rms.info> M2P 2A8
+1 416 646 3304 x110

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html