-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andy Colson
Sent: Tuesday, September 06, 2011 1:55 PM
To: Thom Brown
Cc: Gauthier, Dave; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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.