Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.8.8/8.7.5) with ESMTP id KAA29763 for ; Mon, 3 Aug 1998 10:08:05 -0400 (EDT) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA25908; Mon, 3 Aug 1998 10:07:57 -0400 (EDT) To: Mark Nielsen cc: pgsql-sql@postgresql.org Subject: Re: how do I update a field with a particular oid? In-reply-to: Your message of Sun, 2 Aug 1998 21:52:37 -0400 (EDT) <199808030152.VAA24510@www.tcu-inc.com> Date: Mon, 03 Aug 1998 10:07:57 -0400 Message-ID: <25906.902153277@sss.pgh.pa.us> From: Tom Lane Mark Nielsen 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