Unique constraint with Hamming difference in postgres

user_11251373 0
Viewed 94

Last column image_hash contains image hash of image files(varchar).

My goal is to create unique constraint on this column but it should follow one specific condition.

For example if image_hash 1 and image_hash 2 are the same - then similarity is 1

If they are completely different - similarity →0

For similarity I guess Hamming difference should suit well.

Condition is:

If similarity difference between image_hash 1 and image hash 2 is less then X (for example 0.1), then hashes considered the same and this would be uniqueness violation.

If similarity is bigger then X, then unique constraint is not violated.

I have tried :

SELECT image_hash, similarity(image_hash, '00041dffff101800') AS sml
FROM   archives_imagemodel
WHERE id=431

But it is way to sensitive and I still don’t know how to convert it to unique constraint.

I am bad in Postgres, so that sorry if question is dumb or out of reality

Any ideas?

Thanks

enter image description here

postgresql
1 Answers

That is possible in principle, but it would require writing an extension in C that provides:

  • A similarity operator that implements the Hamming distance. It would return TRUE if the similarly exceeds the threshold.

  • A GiST operator class for text that supports the operator.

Then you can create an exclusion constraint using that operator, which would do exactly what you want. But be warned that you'd have to immerse yourself into the innards of PostgreSQL for that (but you wouldn't have to modify the server).

A more mundane approach would be to use a trigger, but without a GiST index as outlined above that would mean a sequential scan for each data modification, which would kill performance. Moreover, unless you use the SERIALIZABLE isolation level, such triggers are subject to race conditions.

user_64643080
Related