Обсуждение: INSERT question
Hi everyone, I've a little question about using insert statements. I've got a parent table with a "serial" (automatically incrementing integer) primary key. I have a child table with a foreign key which references that primary key. My question is: To insert values into the child table corresponding to an entry in the parent table, how do I get a reference to the serial primary key (so as I can reference it for the foreign key entry) Hope you understand what I mean. This should be a regular occurance and seeing as I'm not an sql guru, I just don't have a clue! Any help would be SOOO appreciated. Brian
Insert the row into the parent table (the one with the primary key), then do a select max(<serial-column>) from parent_table; This will give you the greatest number in the table, that is if everything went ok the number from the row you just inserted. Then insert the row(s) into the second table and use the value you got from the parent table in the foreign key cheers Wilco "Brian" <Brian@McSweeney.iol.ie> schreef in bericht news:1f5f2b44.0111130130.17e8e57a@posting.google.com... | Hi everyone, | I've a little question about using insert statements. I've got a | parent table with a "serial" (automatically incrementing integer) | primary key. I have a child table with a foreign key which references | that primary key. My question is: | | To insert values into the child table corresponding to an entry in the | parent table, how do I get a reference to the serial primary key (so | as I can reference it for the foreign key entry) | | Hope you understand what I mean. This should be a regular occurance | and seeing as I'm not an sql guru, I just don't have a clue! | | Any help would be SOOO appreciated. | Brian
On 13 Nov 2001, Brian wrote:
> Hi everyone,
> I've a little question about using insert statements. I've got a
> parent table with a "serial" (automatically incrementing integer)
> primary key. I have a child table with a foreign key which references
> that primary key. My question is:
>
> To insert values into the child table corresponding to an entry in the
> parent table, how do I get a reference to the serial primary key (so
> as I can reference it for the foreign key entry)
>
> Hope you understand what I mean. This should be a regular occurance
> and seeing as I'm not an sql guru, I just don't have a clue!
I'd say use select currval('<sequence name generated by serial>');
Serial is really just a handy wrapper around a sequence and a column
default, so I'd suggest reading the docs on sequences.
>>>>> "Brian" == Brian <Brian@McSweeney.iol.ie> writes:
Brian> To insert values into the child table corresponding to an Brian> entry in the parent table, how do I get a
referenceto the Brian> serial primary key (so as I can reference it for the Brian> foreign key entry)
To do this in a transaction-safe manner you need to explicitly select
the "parent" row and get it's primary key. That, of course, can be
tricky.
The way I've usually seen this done is to explicitly pull a value from
the sequence and use it for both inserts, e.g.,
select nextval('sequence_name'); insert into foo values ($seqno, ...); insert into bar values ($seqno, ...);
The above is sketchy because I don't know how you are accessing the
database. If you are using Perl or Tcl or somesuch, you will need to
save the value from the select and use in the inserts.
Brian> Hope you understand what I mean. This should be a regular Brian> occurance and seeing as I'm not an sql
guru,I just don't Brian> have a clue!
One question here: when you say "parent" and "child" are you referring
to derived tables or are you just trying to describe the foreign key
constraint? PostgreSQL has "child" tables in the sense of
create table foo ( a serial ); create table bar ( b varchar(32) ) inherits (foo);
roland
-- PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
Yikes! Don't do that. For one thing:
SELECT max(primary_key) FROM parent_table;
Will not use the index in PostgreSQL. If you want to get the maximum
value of an indexed column it is far better to write something like
this:
SELECT primary_key FROM parent_table ORDER BY primary_key DESC LIMIT 1;
Yes, it takes longer to type, but it will use the index and return
your results immediately.
Even better, however, is to use the functions nextval() and currval()
like so:
INSERT INTO parent_table (data) values ('some data');
INSERT INTO child_table (parent, more_data) (currval('parent_table_p_key_seq'), 'more data');
Jason
"Wilco Boschman" <w.boschman@xs4all.nl> writes:
> Insert the row into the parent table (the one with the primary key),
> then do a select max(<serial-column>) from parent_table; This will
> give you the greatest number in the table, that is if everything
> went ok the number from the row you just inserted. Then insert the
> row(s) into the second table and use the value you got from the
> parent table in the foreign key
>
> cheers
>
> Wilco
>
> "Brian" <Brian@McSweeney.iol.ie> schreef in bericht
> news:1f5f2b44.0111130130.17e8e57a@posting.google.com...
> | Hi everyone,
> | I've a little question about using insert statements. I've got a
> | parent table with a "serial" (automatically incrementing integer)
> | primary key. I have a child table with a foreign key which references
> | that primary key. My question is:
> |
> | To insert values into the child table corresponding to an entry in the
> | parent table, how do I get a reference to the serial primary key (so
> | as I can reference it for the foreign key entry)
> |
> | Hope you understand what I mean. This should be a regular occurance
> | and seeing as I'm not an sql guru, I just don't have a clue!
> |
> | Any help would be SOOO appreciated.
> | Brian
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>>>>> "Jason" == Jason Earl <jason.earl@simplot.com> writes:
Jason> Even better, however, is to use the functions nextval() and Jason> currval() like so:
Jason> INSERT INTO parent_table (data) values ('some data'); Jason> INSERT INTO child_table (parent, more_data)
Jason> (currval('parent_table_p_key_seq'), Jason> 'more data');
The above is probably the best for a pure-SQL way with the caveat
that it should be wrapped in a transaction or currval() may not be
what you expect; i.e., another client may add a row and you get a
value different from what you inserted.
roland
-- PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 2001 November 15 03:03 pm, Roland Roberts wrote:
> >>>>> "Jason" == Jason Earl <jason.earl@simplot.com> writes:
>
> Jason> Even better, however, is to use the functions nextval() and
> Jason> currval() like so:
>
> Jason> INSERT INTO parent_table (data) values ('some data');
> Jason> INSERT INTO child_table (parent, more_data)
> Jason> (currval('parent_table_p_key_seq'),
> Jason> 'more data');
>
> The above is probably the best for a pure-SQL way with the caveat
> that it should be wrapped in a transaction or currval() may not be
> what you expect; i.e., another client may add a row and you get a
> value different from what you inserted.
Actually, I don't think you need the transaction. currval() "may not be what
you expect" in a very different way from what you're impling above!
Session1: SELECT nextval('test') # 1
Session2: SELECT nextval('test') # 2
Session1: SELECT currval('test') # 1 !!!!!!!
Session1: SELECT nextval('test') # 3
Kinda counter-intuitive, but fits the definition.
- --
Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iEYEARECAAYFAjv0MkUACgkQCT73CrRXhLEVkwCdGmGvMzc4y3emRPKxWGoGpPiL
WO8AnRekzrv9XVicymCtsplg0QbKFF1M
=iTsp
-----END PGP SIGNATURE-----
Yes that is entirely correct. As you pointed out my example is *very*
dangerous if not wrapped in a transaction. My query clearly should
have looked like this:
BEGIN;
INSERT INTO parent_table (data) values ('some data');
INSERT INTO child_table (parent, more_data) (currval('parent_table_p_key_seq'), 'more data');
COMMIT;
Thanks for pointing that out.
Roland Roberts <roland@astrofoto.org> writes:
> >>>>> "Jason" == Jason Earl <jason.earl@simplot.com> writes:
>
> Jason> Even better, however, is to use the functions nextval() and
> Jason> currval() like so:
>
> Jason> INSERT INTO parent_table (data) values ('some data');
> Jason> INSERT INTO child_table (parent, more_data)
> Jason> (currval('parent_table_p_key_seq'),
> Jason> 'more data');
>
> The above is probably the best for a pure-SQL way with the caveat
> that it should be wrapped in a transaction or currval() may not be
> what you expect; i.e., another client may add a row and you get a
> value different from what you inserted.
>
> roland
> --
> PGP Key ID: 66 BC 3B CD
> Roland B. Roberts, PhD RL Enterprises
> roland@rlenter.com 76-15 113th Street, Apt 3B
> roland@astrofoto.org Forest Hills, NY 11375
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Thu, Nov 15, 2001 at 03:03:00PM -0500, Roland Roberts wrote:
> >>>>> "Jason" == Jason Earl <jason.earl@simplot.com> writes:
>
> Jason> Even better, however, is to use the functions nextval() and
> Jason> currval() like so:
>
> Jason> INSERT INTO parent_table (data) values ('some data');
> Jason> INSERT INTO child_table (parent, more_data)
> Jason> (currval('parent_table_p_key_seq'),
> Jason> 'more data');
>
> The above is probably the best for a pure-SQL way with the caveat
> that it should be wrapped in a transaction or currval() may not be
> what you expect; i.e., another client may add a row and you get a
> value different from what you inserted.
This is one of the only FMAQ I've ever seen: (Frequently Mis-Answered
Question)
Read the docs for currval(): it returns the value _last sent to
this connection_, so is multiuser safe. The _only_ caveat is that in
the presence of connection pooling, your problem as described might
occur. However, since the two inserts above _should_ be wrapped in a
transaction, I'd say any connection pool that shares a connection with
an open transaction on it is broken, anyway.
Ross
Let me say this again, since my previous post is probably unclear:
The code below is safe, even without a transaction, _if it runs in one,
unshared connection to the DB_. So the danger is much less than you
might think. Adding the transaction is good (though nextval() _does not_
rollback, BTW: you get holes in the sequence) because it protects your
data integrity (no orphan parent records if child fails for other reasons)
and lets any layers in between know that these go together, not because
it's needed to cover the multiuser case.
Ross
On Thu, Nov 15, 2001 at 02:28:26PM -0700, Jason Earl wrote:
>
> Yes that is entirely correct. As you pointed out my example is *very*
> dangerous if not wrapped in a transaction. My query clearly should
> have looked like this:
>
> BEGIN;
> INSERT INTO parent_table (data) values ('some data');
> INSERT INTO child_table (parent, more_data)
> (currval('parent_table_p_key_seq'),
> 'more data');
> COMMIT;
>
> Thanks for pointing that out.
>
> Roland Roberts <roland@astrofoto.org> writes:
>
> > >>>>> "Jason" == Jason Earl <jason.earl@simplot.com> writes:
> >
> > Jason> Even better, however, is to use the functions nextval() and
> > Jason> currval() like so:
> >
> > Jason> INSERT INTO parent_table (data) values ('some data');
> > Jason> INSERT INTO child_table (parent, more_data)
> > Jason> (currval('parent_table_p_key_seq'),
> > Jason> 'more data');
> >
> > The above is probably the best for a pure-SQL way with the caveat
> > that it should be wrapped in a transaction or currval() may not be
> > what you expect; i.e., another client may add a row and you get a
> > value different from what you inserted.
> >
> > roland
> > --
> > PGP Key ID: 66 BC 3B CD
> > Roland B. Roberts, PhD RL Enterprises
> > roland@rlenter.com 76-15 113th Street, Apt 3B
> > roland@astrofoto.org Forest Hills, NY 11375
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Thanks for being patient with me. I must have fallen on my head yesterday or something. I *knew* currval and nextval were backend specific. I got confused because I am working on a middleware layer with a connection pool that grabs a connection and holds it until the transaction is finished. Each new transaction might grab a different backend, but you won't get a new backend until your transaction is finished. In other words, in my application the easiest way to insure that currval and nextval work like they should is to start a transaction (which grabs a backend connection). Apparently this has led to some very fuzzy thinking on my part. My apologies for sending contradictory and confusing posts to the list, and thanks for clearing that up. Jason "Ross J. Reedstrom" <reedstrm@rice.edu> writes: > Let me say this again, since my previous post is probably unclear: > > The code below is safe, even without a transaction, _if it runs in one, > unshared connection to the DB_. So the danger is much less than you > might think. Adding the transaction is good (though nextval() _does not_ > rollback, BTW: you get holes in the sequence) because it protects your > data integrity (no orphan parent records if child fails for other reasons) > and lets any layers in between know that these go together, not because > it's needed to cover the multiuser case. > > Ross
No apologies necessary: if anything, _I_ was the one getting a little too gruff. In fact, _this_ time we did uncover a new aspect of this problem: connection pooling can get in the way. Ross On Fri, Nov 16, 2001 at 09:56:03AM -0700, Jason Earl wrote: > > Thanks for being patient with me. I must have fallen on my head > yesterday or something. I *knew* currval and nextval were backend > specific. I got confused because I am working on a middleware layer > with a connection pool that grabs a connection and holds it until the > transaction is finished. Each new transaction might grab a different > backend, but you won't get a new backend until your transaction is > finished. > > In other words, in my application the easiest way to insure that > currval and nextval work like they should is to start a transaction > (which grabs a backend connection). > > Apparently this has led to some very fuzzy thinking on my part. My > apologies for sending contradictory and confusing posts to the list, > and thanks for clearing that up. > > Jason