Finding the hash value of a row in postgresql

Question

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

Solution 1

Cast the row to text and use md5 to make a hash:

SELECT
    md5(CAST((f.*)AS text))
FROM
    foo f;

Solution 2

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.

Comments

[Arun P Johny]

The problem I'm facing is I'm working on a legacy system, where data can come from a import or through add/edit pages and last modified column on all the data is not dependable. That is the reason I'm checking for some kind of hash technique to check for any changes.

[matt b]

With this approach, the database trigger would be determining and writing the last_modified time - not the clients putting data in this table. The clients won't know about this column, and shouldn't - your database would manage it itself.

[Jelen]

OR, shorter SELECT md5(f::text) FROM f

[john mich]

@frank-heikens is there a way to get selective columns and cast them to text? Example- md5(CAST(col_1,col_2,col_3)AS text)

[datico]

@johnmich well, md5( (col_1 || col_2 || col_3)::text ) works for me under postgres 12.

[john mich]

Thanks, datico.

[cstork]

@johnmich @datico Careful! This way different columns can produce the same string, e.g. '' || '' || 'a' = 'a' || '' || ''. Better to use md5(row(col_1,col_2,col_2)::text).