Thread: INSERT question

INSERT question

From
Brian@McSweeney.iol.ie (Brian)
Date:
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


Re: INSERT question

From
"Wilco Boschman"
Date:
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




Re: INSERT question

From
Stephan Szabo
Date:
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.



Re: INSERT question

From
Roland Roberts
Date:
>>>>> "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


Re: INSERT question

From
Jason Earl
Date:
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


Re: INSERT question

From
Roland Roberts
Date:
>>>>> "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


Re: INSERT question

From
"Andrew G. Hammond"
Date:
-----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-----


Re: INSERT question

From
Jason Earl
Date:
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)


Re: INSERT question

From
"Ross J. Reedstrom"
Date:
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


Re: INSERT question

From
"Ross J. Reedstrom"
Date:
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


Re: INSERT question

From
Jason Earl
Date:
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


Re: INSERT question

From
"Ross J. Reedstrom"
Date:
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