Menu
Is there a way to get the hash code of a row in postgresql?
I need to export some data only if there is some changes in the data after the last export, the last exported data rows can be stored in a table, the when the again I need to export the data I can get the hash values of all the data and export only those rows who has a different hash value than the last export.
Is it possible to achieve using postgresql?
Thank you
851
In Windows File Explorer select the files you want the hash values calculated for, click the right mouse button, and select Calculate Hash Value, then select the appropriate hash type from the pop-up sub-menu (e.g. MD5). The values will then be calculated and displayed.
PostgreSQL's hash function maps any database value to a 32-bit integer, the hash code (about 4 billion possible hash codes). A good hash function can be computed quickly and "jumbles" the input uniformly across its entire range. The hash codes are divided to a limited number of buckets.
Use a trigger to set the hash column on insert and update. For SHA-256, use the pgcrypto extension module's digest function. Since you haven't specified your PostgreSQL version I'll assume you're using the current 9.2 in the following examples. Note that the CREATE EXTENSION function must be run as a superuser.
The PostgreSQL MD5() function is used to evaluate the MD5 hash of a string and subsequently return the result. The result is generally in hexadecimal form. Syntax: MD5(string) Let's analyze the above syntax: The string argument is the string of which the MD5 hash is calculated.
Cast the row to text and use md5 to make a hash:
SELECT
md5(CAST((f.*)AS text))
FROM
foo f;
An alternative approach would be to set an ON INSERT OR UPDATE trigger which would insert the current timestamp into a last_modified column, and then simply querying based on this column when your import process runs.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
