David Gard David Gard - 3 years ago 95
SQL Question

Add a unique constraint but ignore existing table data

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);

The query is valid, however on one database the existing data does not meet the constraint, and so the query fails -

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.

It is expected that there would be a few duplicates, but unfortunately I cannot simply delete or alter them.

Is there any way to add the constraint as required, while ignoring the existing data in the table?

Answer Source

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.

Something like:

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.

Doc: https://www.postgresql.org/docs/9.6/static/indexes-partial.html

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download