PostgreSQL removing duplicates without id or unique_key [duplicate]

Question

I'd like to know how I can remove the exact duplicated rows in the table and keep only one. e.g. this table.

to

Most of the threads, I read, have utilised id or unique_key which I don't have in this case.

EDIT: when I said remove I mean delete those records from the table and again I don't have id to make the reference to create the condition to keep one record. Sorry for the confusion.

Thank you in advance.

This may be the same question as other threads. However, they failed to explain what ctid is which fa06 succeeded to deliver that. So, I would say that what i'm asking using the same word but different question. Pls remove "marked duplicate". Thanks.

Answer 1

You can try below

If you need to specifically target the duplicated records, you can make use of the internal ctid field, which uniquely identifies a row:

DELETE FROM yourtablename
WHERE ctid NOT IN (
  SELECT MIN(ctid)
  FROM yourtablename
  GROUP BY date, value, label,sequence
)