Re: how do I update a field with a particular oid?

Mark Nielsen <psql@www.tcu-inc.com> writes:
> Anyways, my question is, I want to update specific rows according to their
> oids. Oh, I am also using the perl module Pg. I can find out the oid of
> the data retrieved, but I want to update data according to their oid.

There's more to this than meets the eye, actually.  I just went through
it, and while it's easy to make it work, making it work *efficiently*
is another story.  The main thing is that you must create an index on
OID.  Without that, the system resorts to sequential scan of the whole
table to locate the right row --- there are no special smarts about
finding rows by OID, it turns out.  So:

    create index table_oid_index on table using btree (oid);

Now, to find out OID of an interesting row, you do something like:

    select oid,* from table where (conditions);

To update a row targeted by OID, eg OID 123456:

    update table set ... where oid = 123456::oid;

Note the explicit cast of the integer value to type OID.  For some
reason the OID index won't be used unless you do that.  (I think this
is probably a bug, but that's how the current sources behave.)

            regards, tom lane