0
303
ViewsUnique constraint with Hamming difference in postgres
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
1 answers
Answer question0
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
TRUEif the similarly exceeds the threshold.A GiST operator class for
textthat 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.