I wish to add a unique constraint to a table using the following query -
ALTER TABLE events ADD CONSTRAINT events_timestamp_request_session_key UNIQUE (event_timestamp, request_id, session_id);
ERROR: could not create unique index "events_timestamp_request_session_key"
DETAIL: Key (event_timestamp, request_id, session_id)=(2017-07-05 14:53:25.475246+00, a55df6-8533e322cd-aa9d57-87e2, 132443) is duplicated.
You can use a partial index for this, its not a particularly nice solution but it will work until you can correct your old data.
CREATE UNIQUE INDEX events_timestamp_request_session_key ON events (event_timestamp, request_id, session_id) WHERE event_timestamp >= '2017-07-01'::timestamp;
where the time is the start of your clean data.
The where clause limits the index to only looking at records with a newer event timestamp. The old records are excluded from the index entirely so don't get considered for uniqueness checks.