Re: FAQ addition: deleteing all but one unique row - Mailing list pgsql-patches
From | greg@turnstep.com |
---|---|
Subject | Re: FAQ addition: deleteing all but one unique row |
Date | |
Msg-id | 726df5e46fe04395a0a5e87fd56e2903@biglumber.com Whole thread Raw |
In response to | Re: FAQ addition: deleteing all but one unique row (Kris Jurka <books@ejurka.com>) |
Responses |
Re: FAQ addition: deleteing all but one unique row
(Bruce Momjian <pgman@candle.pha.pa.us>)
|
List | pgsql-patches |
-----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-----
pgsql-patches by date: