PostgreSQL removing duplicates without id or unique_key

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
)

Answer 2

you could do in this case

SELECT DISTINCT * FROM mytable

This will take only distinct rows and produce the same output, given the input that you shared.

However, note that this only works if all the fields are comparable for equality.

For example, if your table has json columns, the above will not work because postgresql can't know how to compare two json objects for equality.

Answer 3

Select Distinct * from mytable

Answer 4

DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
             FROM   dupes b
             WHERE  a.key = b.key);

As taken from Delete Duplicate Records in PostgresSQL answer