On 6 September 2011 18:39, Gauthier, Dave <dave.gauthier@xxxxxxxxx> 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
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
