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