[GENERAL] SQL-question: returning the id of an insert querry
-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/rhcdPkvkZVZzNY0RApmDAJ4k4MY/zKvH2862MuHSIjDtsmIs3QCfRzaR 0zDc1bIQAOMpLurvRZ2V8JY= =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
Re: [GENERAL] SQL-question: returning the id of an insert querry
After you've done the insert on the address table, you can use
currval('address_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 <[EMAIL PROTECTED]> 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
pgp0.pgp
Description: PGP signature
Re: [GENERAL] SQL-question: returning the id of an insert querry
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Martijn van Oosterhout wrote:
> After you've done the insert on the address table, you can use
> currval('address_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/rkMwPkvkZVZzNY0RAnajAJ0ePCTi/UODhGAxOs5NuptZAT0tUgCgpNAz
Oqh8rM934O3SRRzv4Mh9S4I=
=E71z
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Question on Select
Hi, I have two tables, Table A: item_id Table B: item_id, ref_code and i want to list all ref_codes in table B that are not referenced by Table A. Table A has about 3million records./ table B 200 What is the best way to do that ? Thanks Alex ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Question on Select
select ref_code from tab_b where not exists (select * from tab_a where item_id = tab_b.item_id); make sure u have index on item_id in tab_a . the above sql is quite efficient , other method is to use left join. Alex wrote: Hi, I have two tables, Table A: item_id Table B: item_id, ref_code and i want to list all ref_codes in table B that are not referenced by Table A. Table A has about 3million records./ table B 200 What is the best way to do that ? Thanks Alex ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SQL-question: returning the id of an insert querry
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('address_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
Re: [GENERAL] SQL-question: returning the id of an insert querry
Scott Chapman <[EMAIL PROTECTED]> 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('address_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
Re: [GENERAL] SQL-question: returning the id of an insert querry
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('address_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 ()
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL]
Hello, Can you please help me in understanding how constants are stored in postgresql and how the Datum data structure is used for the same. Thanks Shalu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] question regarding constant values
Hello, Can you please help me in understanding how constant values are stored in postgresql. What is the Datum Data Structue and how is it used? Thanks Shalu Gupta ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
