Search code examples
sqlpostgresqlconstraints

Is there any way to enforce a unique constraint on DB from now onwards, without deleting the old duplicate records?

I'm working with PostgreSQL and I want to enforce a particular column to be unique.

But this table has some legacy data in which duplicate of the column are there and they have to be retained unmodified.

So when I tried to enforce the constraint it is throwing error saying that the table already violates the constraint and the rule is not enforceable.

How to enforce a unique constraint on DB from now onwards, without deleting the old duplicate records?

Solution

  • If this oid is just a normal column and using a sequence, you can use a conditional unique constraint:

    CREATE UNIQUE INDEX u_column ON your_table(your_unique_column)
WHERE oid > _current_max_value;

    Just check the value for your oid where the unique constraint should start. Only the new values must be unique.