Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dups (
  id INTEGER,
  creation_date TIMESTAMP,
  col1 VARCHAR(1),
  col2 VARCHAR(1),
  col3 VARCHAR(1)
);

INSERT INTO dups
  (id, creation_date, col1, col2, col3)
VALUES
  ('1', '2021-04-21', 'a', 'b', 'e'),
  ('2', '2021-04-23', 'a', 'b', 'e'),
  ('3', '2021-04-21', 'c', 'd', 'e');

CREATE TABLE



INSERT 0 3


DELETE FROM dups d1
USING dups d2
WHERE (d2.col1, d2.col2, d2.col3) = (d1.col1, d1.col2, d1.col3)
  AND d2.creation_date < d1.creation_date;

DELETE 1


SELECT * FROM dups;


id
creation_date
col1
col2
col3


1
2021-04-21 00:00:00
a
b
e

3
2021-04-21 00:00:00
c
d
e



SELECT 2