0

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?

Improve this question
6
  • 1
    Does this answer your question? Can I add a UNIQUE constraint to a PostgreSQL table, after it's already created? Commented Sep 14, 2023 at 7:28
  • Does the table have a column to record the timestamp of when the row was created? If so, then a unique partial index conditioned on row creation time could be used. This wouldn't prevent new rows from having column values that match others created prior to the cutoff time, but would ensure that all values in rows created after the cutoff are unique. If such a timestamp column doesn't exists, then ot could be added and the preceding approach applied. Commented Sep 14, 2023 at 7:52
  • @JohnH There is no time stamp but there oid which is serial which can be made use of. How does the alter table query look like Commented Sep 14, 2023 at 9:51
  • timestamp and oid are not at all the same concept. Timestamping give for each rows into the table a "time wartermark" that you can use to "classify" old rows from the new ones, each UPDATEs will adjust the value of the timestamp to a new one with the actual time (using UTC...) Commented Sep 14, 2023 at 11:10
  • @SQLpro yes but we have no time stamp and we will have to use serial oids instead of timestamp. But how to do it? Commented Sep 14, 2023 at 11:36

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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

Improve this answer

4 Comments

Can you please give an example or link of procedural referential integrity to do this (using triggers).
You have to code DML triggers in child table for INSERT and UPDATE (that verify the existence of the ref key into the parent table), then code DML triggers in parent table for UPDATE and DELETE to verify if PK is modified that there is no rows into the child table), and last create two DDL triggers in the parent table, one for ALTER into that prohibits any modification of the structure of the PK and the other to prevent the DROP if any FK value persists in the child table
A conditional unique index would do the trick
More precisely a FILTERED unique index...
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;
Improve this answer

1 Comment

I mean oids will only repeat after 2^32 numbers, so I think it is fine.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.