Re: Does INSERT inserts always at the end ?

florence(dot)henry(at)obspm(dot)fr (Florence HENRY) wrote:
>
> Hello,
>
> well, almost everything is in the subject !
>
> I have to fill 2 tables (more complicated than in the example !):
>
> CREATE TABLE A (
> id serial primary key,
> foo text);
>
> CREATE TABLE B (
> id serial references A,
> bar text);
>
> I fill A with :
> INSERT into A VALUES (DEFAULT, "toto");
>
> Then I need to retreive the "A.id" that was given to A, in order to give it
> to B.id. If I was doing this by hand, it would have been quite easy, but I'm
> doing this with a script.

Define what you mean by "with a script." If you've a db handle open
with, say, Perl's DBI, you could simply do a select on currval() for
the sequence and get it. This is immune to other transactions. But
if, by "script" you mean, say, from a shell script, where you're
feeding commands to psql from stdin or some-such, well... I suppose you
could "echo 'mumble; select currval(blurfl)' |psql" and capture it.
(Caveat: I haven't tried this. I'm just guessing.)

>
> So, if I make a SELECT id from A; and take the last row, will it *always*
> be the row that I've just inserted.

I was told just a few days ago to always regard data in a table as
"unordered." Going by that philosophy: No, you cannot. You
*certainly* cannot if more than one session/task is operating on the
table.

Jim