CODEWITHSUNDEEP
postgresqlduplicatessql-delete
ThreeAccents
ThreeAccents

Reputation: 1882

Delete all records that violate new unqiue constraint

I have a table that has the following fields

----------------------------------
|  id  |  user_id   |   doc_id   |
----------------------------------

I want to create a new unique constraint to make sure that there are no repeat user_id and doc_id records. Aka a user can only be linked to a doc one time. That is simple enough.

ALTER TABLE mytable
    ADD CONSTRAINT uniquectm_const UNIQUE (user_id, doc_id);

The issue is I have records that currently violate that constraint. I was wondering if there is an easy way to query for those records or to tell postgres just delete anything that violates the constraint.

Upvotes: 2

Views: 1316

Answers (2)

skalee
skalee

Reputation: 12675

I would modify the query by @JNevill like this:

SELECT *
FROM
   (
      SELECT id, user_id, doc_id,
        ROW_NUMBER() OVER (PARTITION BY user_id, doc_id ORDER BY id ASC) AS num
      FROM mytable
   )
WHERE num > 1;

The two differences that really matter are:

  1. I'm using ROW_NUMBER() instead of COUNT()
  2. ORDER BY which follows PARTITION BY

With COUNT(*) you get the total number of conflicting rows for each user_id, doc_id pair. But with ROW_NUMBER() you get them ordered 1, 2, 3, etc, therefore final dup_num > 1 will give you minimum rows which need to be deleted to satisfy your constraint, not all the problematic ones. Moreover, by changing ORDER BY criteria you decide whether you get the newest, the oldest or what kind of rows (in the above example, the newest ones that would violate the constraint).

Upvotes: 0

JNevill
JNevill

Reputation: 50218

Identifying records that violate your new key:

SELECT *
FROM
   (
      SELECT id, user_id, doc_id
         , COUNT(*) OVER (PARTITION BY user_id, doc_id) as unique_check
      FROM mytable
   )
WHERE unique_check > 1;

Then you can figure out from those duplicates, which should be deleted and perform the delete.

To my knowledge there is no other way to perform this since any automated "Delete any duplicates" command would leave the database engine to decide which of the two-or-more duplicate records to get rid of.

If the entire record is a duplicate (all columns match) then you could just create a new table with your new unique constraint and do a INSERT INTO newtable SELECT DISTINCT * FROM oldtable but I'm betting that isn't the case.

Upvotes: 3

Related Questions