The identification and deleting of the records using ctids seems to have worked fine. Thanks ! -----Original Message----- From: Andy Colson [mailto:andy@xxxxxxxxxxxxxxx] Sent: Tuesday, September 06, 2011 1:55 PM To: Thom Brown Cc: Gauthier, Dave; pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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@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
