This is part of my table and how can I delete the duplicate row? I use CTE clause but it deleted all records of id #8378563200 on 4/25/2016.

CodePudding user response：

Use:

DELETE
FROM table1
WHERE ctid IN (SELECT ctid
               FROM (SELECT ctid,
                            ROW_NUMBER() OVER (
                                PARTITION BY Id, SleepDay,TotalMinutesAsleep,TotalTimeInBed ) AS rn
                     FROM table1) t
               WHERE rn > 1);

Replace table1 with your own table name.

CodePudding user response：

Without column(s) to identify a unique row?
Then you could use ctid.

ctid

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. A primary key should be used to identify logical rows

For example:

delete 
 from SleepLogs log1
using SleepLogs log2
where log2.Id = log1.Id
  and log2.SleepDay = log1.SleepDay
  and log2.TotalMinutesAsleep = log1.TotalMinutesAsleep
  and log2.TotalTimeInBed = log1.TotalTimeInBed
  and log2.ctid < log1.ctid;

1 rows affected

select * from SleepLogs

id	sleepday	totalminutesasleep	totaltimeinbed
8378563200	2016-04-20	381	409
8378563200	2016-04-21	396	417
8378563200	2016-04-22	441	469
8378563200	2016-04-23	565	591
8378563200	2016-04-24	458	492
8378563200	2016-04-25	388	402
8378563200	2016-04-26	550	584
8378563200	2016-04-27	531	600

Test on db<>fiddle here