WITH duplicates AS (
    SELECT ctid,
        ROW_NUMBER() OVER (
            PARTITION BY entities, attributename
            ORDER BY ehrreferenceid
        ) as rn
    FROM ehr.t_ehr_reference
)
DELETE FROM ehr.t_ehr_reference
WHERE ctid IN (
    SELECT ctid FROM duplicates WHERE rn > 1
);

CREATE UNIQUE INDEX IF NOT EXISTS UI_T_EHR_REFERENCE
ON EHR.T_EHR_REFERENCE("entities", "attributename");