Skip site navigation (1) Skip section navigation (2)

Re: Does INSERT inserts always at the end ?

From: jseymour(at)LinxNet(dot)com (Jim Seymour)
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Does INSERT inserts always at the end ?
Date: 2004-05-21 20:51:13
Message-ID: 20040521205113.D0AF9430E@jimsun.LinxNet.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Robert TreatDate: 2004-05-21 20:55:20
Subject: Re: Porting SQL Server 2000 database to PostgreSQL
Previous:From: Vivek KheraDate: 2004-05-21 20:49:55
Subject: Re: pg_autovacuum seems to be a neat freak and cleans way too much

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group