如何对所有列进行md5而不管类型 [英] How to md5 all columns regardless of type

问题描述

我想创建一个sql查询(或plpgsql)，该查询将对所有给定的行md5()进行排序，而与类型无关.但是，在下面，如果一个为空，则哈希为空:

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.

问题在于它处理串联null的方式.例如:

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

如果知道类型，则可以使用合并或CASE语句；但是，我有很多表，而且我不会提前知道每一列的类型.

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.

推荐答案

对此有更优雅的解决方案.

There is much more elegant solution for this.

在Postgres中，允许在SELECT中使用表名，并且其类型为ROW.如果将其强制转换为类型TEXT，它将给出所有串联在一起的字符串，这些字符串实际上是JSON.

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.

有了这个，您可以按以下方式获得所有列的md5:

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

SELECT md5(mytable::TEXT)
FROM mytable

如果只想使用某些列，请使用ROW构造函数并将其强制转换为TEXT:

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

关于此解决方案的另一个不错的特性是md5与NULL相对于空字符串而言会有所不同.

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

必填 SQLFiddle .

这篇关于如何对所有列进行md5而不管类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！