Question 246943 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

3

Answers


  1. 0

    PostGreSQL has no way in a referential integrity constraint to specify that passed rows should not be checked, but that the constraint only applies to new rows (the equivalent of the FK "not trusted" constraints of Microsoft SQL Server for example with the CHECK option).

    However you can use procedural referential integrity to do this (using triggers).

    Login or Signup to reply.
  2. 0

    You can’t have a unique constraint, but you can have a partial unique index.

    OIDs are not guaranteed to be unique nor in incremental order, see this post.

    You can add a new column, type boolean, exclude_from_uniqueness. Set to True for existing records (or for any records for which you want to allow duplicates), and leave null or false for all others.

    And at last create the partial unique index only for rows that are not excluded:

    create unique index pseudo_uk on mytable(myfield) where exclude_from_uniqueness is distinct from true;

    PS: if the oid you mention is controlled by you (i.e. not the system wide oid) or if you like poisonous code that can blow up in a few years for no apparent reasons, you can of course do

    create unique index pseudo_uk on mytable(myfield) where oid > 1234;
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search