SQL-question: returning the id of an insert querry

[Andreas Fromm]

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

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

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

iD8DBQE/rhcdPkvkZVZzNY0 RApmDAJ4k4MY/zKvH2862MuHSIjD tsmIs3QCfRzaR
0zDc1bIQAOMpLur vRZ2V8JY=
=kgaA
-----END PGP SIGNATURE-----
---------------------------(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

[Martijn van Oosterhout]

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:

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
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ "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

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

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

Martijn van Oosterhout wrote:

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,

...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 ma*******@postg resql.org)

[Scott Chapman]

On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:

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.

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?

http://archives.postgresql.org

[Doug McNaught]

Scott Chapman <sc********@mis chko.com> writes:

On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:

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.

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

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

-Doug

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

[Alvaro Herrera]

On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:

On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:

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.

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

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]

On Sunday 09 November 2003 10:52, Alvaro Herrera wrote:

On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:

On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:

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.

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

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

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]

On Mon, Nov 10, 2003 at 08:09:29AM -0800, Scott Chapman wrote:

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?

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]

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: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Scott Chapman
Sent: Monday, November 10, 2003 10:09 AM
To: Alvaro Herrera
Cc: Martijn van Oosterhout; Andreas Fromm; pg***********@p ostgresql.org
Subject: Re: [GENERAL] SQL-question: returning the id of an insert
querry
On Sunday 09 November 2003 10:52, Alvaro Herrera wrote:

On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:

On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:

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.

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

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

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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Scott Chapman]

On Monday 10 November 2003 08:23, David Green wrote:

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

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