CODEWITHSUNDEEP
sqlpostgresqlmd5plpgsql
brianray
brianray

Reputation: 1169

How to md5 all columns regardless of type

I would like to create a sql query (or plpgsql) that will md5() all given rows regardless of type. However, below, if one is null then the hash is null:

UPDATE thetable 
    SET hash = md5(accountid || accounttype || createdby || editedby);

I am later using the hash to compare uniqueness so null hash does not work for this use case.

The problem was the way it handles concatenating nulls. For example: 

thedatabase=# SELECT accountid || accounttype || createdby  || editedby 
                 FROM thetable LIMIT 5;  

1Type113225  
<NULL>
2Type11751222 
3Type10651010 
4Type10651

I could use coalesce or CASE statements if I knew the type; however, I have many tables and I will not know the type ahead of time of every column.

Upvotes: 17

Views: 42770

Answers (4)

Alexandre Custodio
Alexandre Custodio

Reputation: 1

select MD5(cast(p as text)) from fiscal_cfop as p

Upvotes: 0

mvp
mvp

Reputation: 116177

There is much more elegant solution for this.

In Postgres, using table name in SELECT is permitted and it has type ROW. If you cast this to type TEXT, it gives all columns concatenated together in string that is actually JSON.

Having this, you can get md5 of all columns as follows:

SELECT md5(mytable::TEXT)
FROM mytable

If you want to only use some columns, use ROW constructor and cast it to TEXT:

SELECT md5(ROW(col1, col2, col3)::TEXT)
FROM mytable

Another nice property about this solution is that md5 will be different for NULL vs. empty string.

Obligatory SQLFiddle.

Upvotes: 57

najczuk
najczuk

Reputation: 163

You can also use something else similar to mvp's solution. Instead of using ROW() function which is not supported by Amazon Redshift...

Invalid operation: ROW expression, implicit or explicit, is not supported in target list;

My proposition is to use NVL2 and CAST function to cast different type of columns to CHAR, as long as this type is compatible with all Redshift data types according to the documentation. Below there is an example of how to achieve null proof MD5 in Redshift.

SELECT md5(NVL2(col1,col1::char,''), 
           NVL2(col2,col2::char,''), 
           NVL2(col3,col3::char,''))
FROM mytable

This might work without casting second NVL2 function argument to char but it would definately fail if you'd try to get md5 from date column with null value. I hope this would be helpful for someone.

Upvotes: 5

Sam Texas
Sam Texas

Reputation: 1275

Have you tried using CONCAT()? I just tried in my PG 9.1 install:

SELECT CONCAT('aaaa',1111,'bbbb');     => aaaa1111bbbb
SELECT CONCAT('aaaa',null,'bbbb');     => aaaabbbb

Therefore, you can try:

SELECT MD5(CONCAT(column1, column2, column3, column_n))    => md5_hash string here

Upvotes: 2

Related Questions