The identification and deleting of the records using ctids seems to have worked fine.
Thanks !
-----Original Message-----
From: Andy Colson [mailto:andy@squeakycode.net]
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
On 9/6/2011 12:44 PM, Thom Brown wrote:
> On 6 September 2011 18:39, Gauthier, Dave <dave.gauthier@intel.com
> <mailto:dave.gauthier@intel.com>> wrote:
>
> Hi:____
>
> __ __
>
> If I have a table that has 2 records which are identical with regard
> to all their column values, is there a way to delete one of them,
> leaving one remaining? Is there some unique record_id key of some
> sort I can use for somethign like this?____
>
> __ __
>
> Thanks in Advance!____
>
>
> Yes, identify them by their ctid value.
>
> So get the ctids by running:
>
> SELECT ctid, *
> FROM my_table
> WHERE <clause to identify duplicate rows>
>
> You will see entries which look like "(7296,11)".
>
> You can then delete the row by referencing it in the DELETE statement.
> For example:
>
> DELETE FROM my_table
> WHERE ctid = '(7296,11)';
>
> It's a shame we don't have a LIMIT on the DELETE clause (looks at hackers).
>
> --
> Thom Brown
> Twitter: @darkixion
> IRC (freenode): dark_ixion
> Registered Linux user: #516935
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
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
