-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Andy Colson Sent: Tuesday, September 06, 2011 1:55 PM To: Thom Brown Cc: Gauthier, Dave; pgsql-general@xxxxxxxxxxxxxx Subject: Re: Deleting one of 2 identical records I wonder.. using the new writeable cte's, could you: with x ( -- id = 5 has two identical rows, but limit 1 select * from orig where id = 5 limit 1; ) delete from x; -Andy ---------------------------------------------------------- By my understanding it is a writeable CTE because the statement inside the CTE can now be INSERT/UPDATE/DELETE (in addition to the SELECT - read only - statement). A CTE is, in some ways, like an immediately materialized view. Any reference to it does not affect the source tables; thus your example likely would not work. It isn't a RULE where "x" is simply an alias for "orig". The real problem is not the language but the table design. The idea of "true duplicates" is generally problematic but when it does occur it is advisable to introduce some kind of artificial key/sequence to allow for direct selection of the row without resorting to internals. Then, it is simply to use the full power of the SELECT statement (with Window functions and CTEs) to identify the rows that are to be deleted and feed the PK from those rows into the DELETE's WHERE clause using a sub-query. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
