Thread: how to determine OID of the row I just inserted???

Hello everybody,

Here's a question I have asked some time ago and Google tells me I'm
not the only one with this problem, but I haven't found a solution yet
:-(. I have a setup like this:

    customer {id serial, name varchar, address bigint}
    person   {id serial, name varchar, address bigint}
    address  {id serial, street varchar}

    customer.address points to address.id
    person.address points to address.id

So, addresses are stored in a seperate table, customer.address and
person.address should have a value that exists in address.id. When I
add an address for customer X, I must do something like:

    insert into address (street) values ('Penny Lane');
    update customer set address = ??? where name = 'X';

If I would do this in pgsql there would be no problem, because the OID
is echoed when the update succeeds. But I use PHP or pl/pgsql (others
have exactly the same problem with JDBC) and I know of no way to solve
this. Something like lastval() IMHO is way too risky. I need something
like a return value:

    catchOID = returnQueryOID('insert into address (street)
        values ('Penny Lane'));
    update customer set address = 'select id from address where
        oid = catchOID' where name = 'X';

Sorry for any syntax errors in my examples (it's half semi code), I
hope you understand the problem. If somebody knows a good solution, IMO
this would be something to put in a FAQ, because exactly the same
question arises when you do an insert on a table where the primary key
is generated automatically and you want to show the result after the
row is updated (which is very common).

TIA for any help, I really need a solution...

On Thu, 6 Feb 2003, Jules Alberts wrote:

> Hello everybody,
>
> Here's a question I have asked some time ago and Google tells me I'm
> not the only one with this problem, but I haven't found a solution yet
> :-(. I have a setup like this:
>
>     customer {id serial, name varchar, address bigint}
>     person   {id serial, name varchar, address bigint}
>     address  {id serial, street varchar}
>
>     customer.address points to address.id
>     person.address points to address.id
>
> So, addresses are stored in a seperate table, customer.address and
> person.address should have a value that exists in address.id. When I
> add an address for customer X, I must do something like:
>
>     insert into address (street) values ('Penny Lane');
>     update customer set address = ??? where name = 'X';
>
> If I would do this in pgsql there would be no problem, because the OID
> is echoed when the update succeeds.

Whoa. You're on about different things. OID is most certainly not what you
want, it is not the value in your id column. What you want is to forget about
OIDs and find the value inserted into id for the row you just inserted.

The serial type is based on sequences so you can query the sequence associated
with it to find it's current value (after running the insert). You do that by
using: SELECT curval('my_serial_column_seq')

Now, I've never used serial, I have only created and used my own sequences for
this task and so I can not say how you know what the sequence underlying the
serial is called. It must be in the documentation somewhere and you'll probably
get a few replies from people who do know.



> But I use PHP or pl/pgsql (others
> have exactly the same problem with JDBC) and I know of no way to solve
> this. Something like lastval() IMHO is way too risky. I need something
> like a return value:
>
>     catchOID = returnQueryOID('insert into address (street)
>         values ('Penny Lane'));
>     update customer set address = 'select id from address where
>         oid = catchOID' where name = 'X';

Ah, I see, so it wasn't such a mistake as I thought. However, the basic idea is
the same; forget about oids and use the serial type [sequence] otherwise you
may as well get rid of it.

> Sorry for any syntax errors in my examples (it's half semi code), I
> hope you understand the problem. If somebody knows a good solution, IMO
> this would be something to put in a FAQ, because exactly the same
> question arises when you do an insert on a table where the primary key
> is generated automatically and you want to show the result after the
> row is updated (which is very common).
>
> TIA for any help, I really need a solution...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
Nigel J. Andrews

Op 6 Feb 2003 (14:25), schreef Nigel J. Andrews <nandrews@investsystems.co.uk>:
> On Thu, 6 Feb 2003, Jules Alberts wrote:
<snip>
> > But I use PHP or pl/pgsql (others
> > have exactly the same problem with JDBC) and I know of no way to solve
> > this. Something like lastval() IMHO is way too risky. I need something
> > like a return value:
> >
> >     catchOID = returnQueryOID('insert into address (street)
> >         values ('Penny Lane'));
> >     update customer set address = 'select id from address where
> >         oid = catchOID' where name = 'X';
>
> Ah, I see, so it wasn't such a mistake as I thought. However, the basic idea is
> the same; forget about oids and use the serial type [sequence] otherwise you
> may as well get rid of it.

Thanks for your reaction Nigel, but my problem remains the same whether
I use OIDs or sequence values. In a more abstract way the problem would
be:

    "how do I determine which row was affected by my last INSERT
    or UPDATE statement"

If you know the primary key value it's easy, you just do

    INSERT INTO customer (id, name) VALUES ('1234', 'Paul');
    SELECT * FORM customer WHERE id = '1234';

But the problem is that in my situation there is no way of knowing the
primary key value. Pgsql very politely echoes the OID of the affected
row. Languages like pl/pgsql and PHP AFAIK, don't. Maybe getting the
lastval() of the primary key sequence would be a solution, but what
happens if someone else accesses the sequence between my INSERT and
SELECT?

So no matter if OIDs are lost with a dump / restore, if they will be
dropped in the future or not, my problem remains...

TIA for any other tips!

"Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> Something like lastval() IMHO is way too risky.

currval() is what you want, and it is *not* risky.  Read the sequence
documentation.

            regards, tom lane

What someone told me was to do this:

    insert into address (street) values ('Penny Lane');
    update customer
        set address =
         (select id
          from address
          where street = 'Penny Lane')
        where name = 'X';

Obviously, there can only be one id for 'Penny Lane'.


2/6/2003 6:08:17 AM, "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> wrote:

>Hello everybody,
>
>Here's a question I have asked some time ago and Google tells me I'm
>not the only one with this problem, but I haven't found a solution yet
>:-(. I have a setup like this:
>
>    customer {id serial, name varchar, address bigint}
>    person   {id serial, name varchar, address bigint}
>    address  {id serial, street varchar}
>
>    customer.address points to address.id
>    person.address points to address.id
>
>So, addresses are stored in a seperate table, customer.address and
>person.address should have a value that exists in address.id. When I
>add an address for customer X, I must do something like:
>
>
>If I would do this in pgsql there would be no problem, because the OID
>is echoed when the update succeeds. But I use PHP or pl/pgsql (others
>have exactly the same problem with JDBC) and I know of no way to solve
>this. Something like lastval() IMHO is way too risky. I need something
>like a return value:
>
>    catchOID = returnQueryOID('insert into address (street)
>        values ('Penny Lane'));
>    update customer set address = 'select id from address where
>        oid = catchOID' where name = 'X';
>
>Sorry for any syntax errors in my examples (it's half semi code), I
>hope you understand the problem. If somebody knows a good solution, IMO
>this would be something to put in a FAQ, because exactly the same
>question arises when you do an insert on a table where the primary key
>is generated automatically and you want to show the result after the
>row is updated (which is very common).
>
>TIA for any help, I really need a solution...
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Tom Lane wrote:
> "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
>>Something like lastval() IMHO is way too risky.
>
> currval() is what you want, and it is *not* risky.  Read the sequence
> documentation.

Tom's absolutely correct, of course. See:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/functions-sequence.html

Another common solution I've used is something like:

create table person(pid serial, name text, aid int);
create table address(aid int, street text);
create sequence address_seq;

Then in PHP (or whatever) do:
     select nextval('address_seq');
and put the value into a variable (let's say you get back 42). Now you can do:

insert into person (name, aid) values ('John Doe', 42);
insert into address values (42, 'Penny Lane');

HTH,

Joe

> 2/6/2003 6:08:17 AM, "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> wrote:
> >Here's a question I have asked some time ago and Google tells me I'm
> >not the only one with this problem, but I haven't found a solution yet
> >:-(. I have a setup like this:
> >
> >    customer {id serial, name varchar, address bigint}
> >    person   {id serial, name varchar, address bigint}
> >    address  {id serial, street varchar}
> >
> >    customer.address points to address.id
> >    person.address points to address.id

i finally figured out the the serial datatype is really an int4
(with auto-incrementing insert as default):

    \d team
                           Table "team"
     Column |  Type   |                    Modifiers
    --------+---------+---------------------------------------------------
     id     | integer | not null default nextval('"_track_id_seq"'::text)
    <snip>

they're int (int4) not int8. at least, on my debian box, that is.


assuming your address table's id is also auto-incremented using
the nextval() function, then here's what you can do --

    insert into address(
        street
    )values(
        'Penny Lane'
    );
    -- now "currval" is available for the id, this session

    update
        customer
    set
        address = currval('address_id_seq')
    where
        id = $yadayada;

===

but here's a question for you -- does each location exist
independelty of who it's a location for? and can customers or
persons not have more than one address? (person->work, home,
vacation, etc; customer->east, hongKong, downtown, etc)

maybe you should have a person.id or customer.id field in your
address table:

    create table address_type(
        id serial,
        name varchar(20),
        seq  smallint, -- for ordering choices on an interface "menu"
        primary key ( id )
    );
    insert into address_type(name,seq)values('Home',10);
    insert into address_type(name,seq)values('Office',20);
    insert into address_type(name,seq)values('Friend',100);
    insert into address_type(name,seq)values('Family',110);
    insert into address_type(name,seq)values('Vacation',200);

    create table address(
        id serial,
        person int4 references person ( id ),
        address_type int4 references address_type ( id ),
        street varchar(40),
        primary key ( id )
    );

then for a particular person,

    insert into address(person,address_type,street)
        select
            p.id,
            t.id,
            'Penny Lane'
        from
            person p,
            address_type t
        where
            p.lname = 'Pfingston'
            -- or whatever you need to specify the ONE person
            and
            t.name = 'Home'
        ;

    then join address.person to person.id, instead of the other
    way 'round.

===

one more note -- having perused "database design for mere
mortals" i agree it's important to separate the subjects from
their attributes...

is "address" really an appropriate name for a table? it's a bit
ambiguous, that term -- can mean "number-and-street" or
"everything needed to get the delivery taken care of", but we
take it to mean the street portion of a location spec:

    329 main street <== address
    suote 701       <== suite
    centralburg     <== city
    idaho           <== province/state
    87654           <== postal code
    usa             <== country code

    329 main street <== address
                    <== suite
    galt's gulch    <== city
    colorado        <== province/state
    77665           <== postal code
    usa             <== country code

these are locations, right?

for any particular geographic location, we consider an address to
be one portion of the total picture. we call ours "location" of
which "address" is a field.

just a thought...

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

Op 6 Feb 2003 (10:08), schreef Tom Lane <tgl@sss.pgh.pa.us>:
> "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> > Something like lastval() IMHO is way too risky.
>
> currval() is what you want, and it is *not* risky.  Read the sequence
> documentation.

I did that, and currval() _is_ the answer :-)

It operates on the current process only (out of curiosity I tested it),
so it's safe.

Thanks a lot!

hi, i think you should just use the pg_lastoid function available in C
or PHP.
in sql you can reference the last oid with :OID


In article <20030206140855.A06E21CB1D9@koshin.dsl.systemec.nl>,
jules.alberts@arbodienst-limburg.nl says...
> Hello everybody,
>
> Here's a question I have asked some time ago and Google tells me I'm
> not the only one with this problem, but I haven't found a solution yet
> :-(. I have a setup like this:
>
>     customer {id serial, name varchar, address bigint}
>     person   {id serial, name varchar, address bigint}
>     address  {id serial, street varchar}
>
>     customer.address points to address.id
>     person.address points to address.id
>
> So, addresses are stored in a seperate table, customer.address and
> person.address should have a value that exists in address.id. When I
> add an address for customer X, I must do something like:
>
>     insert into address (street) values ('Penny Lane');
>     update customer set address = ??? where name = 'X';
>
> If I would do this in pgsql there would be no problem, because the OID
> is echoed when the update succeeds. But I use PHP or pl/pgsql (others
> have exactly the same problem with JDBC) and I know of no way to solve
> this. Something like lastval() IMHO is way too risky. I need something
> like a return value:
>
>     catchOID = returnQueryOID('insert into address (street)
>         values ('Penny Lane'));
>     update customer set address = 'select id from address where
>         oid = catchOID' where name = 'X';
>
> Sorry for any syntax errors in my examples (it's half semi code), I
> hope you understand the problem. If somebody knows a good solution, IMO
> this would be something to put in a FAQ, because exactly the same
> question arises when you do an insert on a table where the primary key
> is generated automatically and you want to show the result after the
> row is updated (which is very common).
>
> TIA for any help, I really need a solution...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>