How to delete duplicate rows without unique ID

Question

Id	SleepDay	TotalMinutesAsleep	TotalTimeInBed
8378563200	4/20/2016	381	409
8378563200	4/21/2016	396	417
8378563200	4/22/2016	441	469
8378563200	4/23/2016	565	591
8378563200	4/24/2016	458	492
8378563200	4/25/2016	388	402 ---> this is the duplicate
8378563200	4/25/2016	388	402
8378563200	4/26/2016	550	584
8378563200	4/27/2016	531	600

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.

Answer 1

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