Geek Answers Handbook

Like md5 all columns regardless of type

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

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

Later, I use a hash to compare uniqueness, so a null hash does not work for this use case.

The problem was how it handles the concatenation of zeros. For instance:

 thedatabase=# SELECT accountid || accounttype || createdby || editedby FROM thetable LIMIT 5; 1Type113225 <NULL> 2Type11751222 3Type10651010 4Type10651

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

+4
source share
3 answers

There is a much more elegant solution for this.

Postgres allows the use of table names in SELECT and it is of type ROW . If you sketch this over the TEXT type, it gives all the columns joined together in a row, which is actually JSON.

After that, you can get md5 all columns as follows:

 SELECT md5(mytable::TEXT) FROM mytable

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

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

Another nice feature in this solution is that md5 will be different for NULL with respect to the empty string.

Required SQLFiddle .

+20
source

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

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

My suggestion is to use the NVL2 and CAST functions to create columns of different types before CHAR, if that type is compatible with all Redshift data types according to the documentation . Below is an example of how to achieve zero proof MD5 in Redshift.

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

This may work without adding the second argument of the NVL2 function to char, but it will definitely fail if you try to get md5 from a date column with a null value. I hope this will be helpful to someone.

+2
source

Have you tried using CONCAT () ? I just tried installing PG 9.1:

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

So you can try:

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

More articles:


All Articles