On 9/6/2011 12:44 PM, Thom Brown wrote:
On 6 September 2011 18:39, Gauthier, Dave <dave.gauth...@intel.com
<mailto:dave.gauth...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general