Re: FAQ addition: deleteing all but one unique row

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message


> You have still not addressed Tom's initial complaint about
> the delete deleting all but one row in the table.  Shouldn't
> there be a "WHERE id=12" on the delete as well?

Yes, thank you, there should! Third time is the charm:

Index: FAQ.html
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/FAQ/FAQ.html,v
retrieving revision 1.164
diff -c -r1.164 FAQ.html
*** FAQ.html                2002/12/05 05:47:44                    1.164
--- FAQ.html                2003/02/10 15:08:00
***************
*** 139,144 ****
--- 139,145 ----
      temporary tables in PL/PgSQL functions?<BR>
       <A href="#4.27">4.27</A>) What replication options are available?<BR>
       <A href="#4.28">4.28</A>) What encryption options are available?<BR>
+      <A href="#4.29">4.29</A>) How can I delete all but one identical row?<BR>


      <H2 align="center">Extending PostgreSQL</H2>
***************
*** 1381,1386 ****
--- 1382,1406 ----
      <I>PASSWORD_ENCRYPTION</I> in <I>postgresql.conf</I>.</LI>
      <LI>The server can run using an encrypted file system.</LI>
      </UL>
+
+     <H4><A name="4.29">4.29</A>) How can I delete all but one identical row?<BR>
+     </H4>
+     <P>Sometimes you have rows that are so identical that a simple WHERE clause cannot
+        distinguish them apart. Each row always has a unique system column named
+        <CODE><SMALL>ctid</SMALL></CODE> that can be used to differentiate them. Use
+        <SMALL>LIMIT 1</SMALL> to get back the ctid of one of the identical rows,
+        then remove all matching rows except the one with that particular ctid:</P>
+ <PRE>
+        DELETE FROM mytable WHERE id=12 AND NOT ctid =
+          (SELECT ctid FROM mytable WHERE id=12 LIMIT 1);
+ </PRE>
+
+     <P>In the above example, all rows in the table named 'mytable' having a value
+        of 12 in the 'id' column will be deleted except for one. Exactly
+        which row is kept should not matter, as they are all otherwise identical.
+        The system column <CODE><SMALL>oid</SMALL></CODE> can also be used, but
+        because tables can be created without an oid column, the use of ctid
+        is preferred.</P>

      <HR>




--
Greg Sabino Mullane  greg@turnstep.com
PGP Key: 0x14964AC8 200302121640

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+SsAZvJuQZxSWSsgRAu4OAJ9SrvLn/Tv9z9/gTMqtn+EflL45BgCeLxO8
uzmdojhtoXVpush7kkoSMn4=
=sPIw
-----END PGP SIGNATURE-----