PostgreSQL checksum field for comparing content

Question

I have a field in a table with a lot of content (text or binary data). If I want to know if another text is equal to this, I can use the checksum to compare the two texts. I can define this field as UNIQUE to avoid duplicate content too.

I doubt that if I create a checksum field, this comparison will speed up, so is PostgreSQL already doing this (without programmer intervention) or do I need to do it manually?

EDIT: Which is better, create a checksum for the TEXT field, use a checksum for it, or two ways is the same?

Answer 1

There is no default “checksum” for large columns in PostgreSQL, you will have to implement it yourself.

Reply to comment

Hash indexes provide fast performance for equality checks. And they are updated automatically. But they are not fully integrated into PostgreSQL (yet), so their use is not recommended - read the manual .

And you cannot request values ​​and use them in your application, for example. You can do this with a checksum column, but you need to add a performance index if your table is large and supports a column. I would use the BEFORE INSERT OR UPDATE trigger for this.

Thus, the hash index may or may not be for you. @ Hijra. the idea certainly fits the problem ...

Answer 2

You can read the Indexes Types manual, because basically you want to do the same thing as the hash index, but with your bare hands. This way you can read about the advantages and disadvantages of the hash index in PostgreSQL.