X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8E9F1475F73 for ; Thu, 30 Jan 2003 20:17:45 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id D25FB475AD4 for ; Thu, 30 Jan 2003 20:17:43 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0V1GQ5u004831; Thu, 30 Jan 2003 20:16:26 -0500 (EST) To: greg@turnstep.com Cc: pgsql-sql@postgresql.org Subject: Re: Delete 1 Record of 2 Duplicate Records In-reply-to: <07f0833692070125b9317094e7008b4f@biglumber.com> References: <07f0833692070125b9317094e7008b4f@biglumber.com> Comments: In-reply-to greg@turnstep.com message dated "Thu, 30 Jan 2003 15:04:20 +0000" Date: Thu, 30 Jan 2003 20:16:26 -0500 Message-ID: <4830.1043975786@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/513 X-Sequence-Number: 11378 greg@turnstep.com writes: > If they are truly identical, then you must use the hidden 'oid' column to > differentiate the two. No need to peek at the oid, just do this: > DELETE FROM test WHERE oid = (SELECT oid FROM test WHERE column_id=5 LIMIT 1); Also, if you're unfortunate enough to have this problem in a table with no OID column, you can play the same trick with the ctid column, which always exists (it's the physical location of the tuple). ctid is dangerous to use for most purposes, because it's not stable over UPDATE or VACUUM FULL, but it works perfectly well for this kind of problem. regards, tom lane