Re: FAQ addition: deleteing all but one unique row - Mailing list pgsql-patches

Is this asked frequently enough to be an FAQ?

---------------------------------------------------------------------------

greg@turnstep.com wrote:
[ There is text before PGP section. ]
>
[ PGP not available, raw data follows ]
> -----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-----
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
[ Decrypting message... End of raw data. ]

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073