postgresql 无论类型如何，如何对所有列进行 md5
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14576010/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to md5 all columns regardless of type
提问by brianray
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:
我想创建一个 sql 查询（或 plpgsql），它将 md5() 所有给定的行，而不管类型如何。但是，在下面，如果一个为空，则哈希为空：
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.
如果我知道类型，我可以使用 coalesce 或 CASE 语句；但是，我有很多表，我不会提前知道每一列的类型。
回答by mvp
There is much more elegant solution for this.
对此有更优雅的解决方案。
In Postgres, using table name in SELECTis 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.
在 Postgres 中，SELECT允许使用表名 in并且它的类型为ROW。如果您将TEXT其强制转换为 type ，它会将所有列连接在一起，实际上是 JSON 字符串。
Having this, you can get md5of all columns as follows:
有了这个，您可以md5按如下方式获取所有列：
SELECT md5(mytable::TEXT)
FROM mytable
If you want to only use some columns, use ROWconstructor and cast it to TEXT:
如果您只想使用某些列，请使用ROW构造函数并将其强制转换为TEXT：
SELECT md5(ROW(col1, col2, col3)::TEXT)
FROM mytable
Another nice property about this solution is that md5will be different for NULLvs. empty string.
这个解决方案的另一个很好的特性是它与空字符串md5不同NULL。
Obligatory SQLFiddle.
强制性SQLFiddle。
回答by najczuk
You can also use something else similar to mvp's solution. Instead of using ROW() function which is not supported by Amazon Redshift...
您还可以使用类似于 mvp 解决方案的其他方法。而不是使用 Amazon Redshift 不支持的 ROW() 函数......
Invalid operation: ROW expression, implicit or explicit, is not supported in target list;
无效操作：目标列表中不支持隐式或显式的 ROW 表达式；
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 proofMD5 in Redshift.
我的提议是使用 NVL2 和 CAST 函数将不同类型的列转换为 CHAR，只要根据文档，这种类型与所有 Redshift 数据类型兼容。下面是如何在 Redshift 中实现空证明MD5的示例。
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.
这可能会在不将第二个 NVL2 函数参数转换为 char 的情况下工作，但如果您尝试从具有空值的日期列中获取 md5，它肯定会失败。我希望这对某人有帮助。
回答by Sam Texas
Have you tried using CONCAT()? I just tried in my PG 9.1 install:
您是否尝试过使用CONCAT()？我刚刚在我的 PG 9.1 安装中尝试过：
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
回答by Alexandre Custodio
select MD5(cast(p as text)) from fiscal_cfop as p
从财政_cfop 中选择 MD5(cast(p as text)) 作为 p
