Comment md5 toutes les colonnes indépendamment du type

Je voudrais créer une requête sql (ou plpgsql) qui md5() tous les lignes indépendamment de leur type. Cependant, ci-dessous, si l'on est null, la valeur de hachage est nulle:

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

Je suis plus tard à l'aide de la table de hachage pour comparer l'unicité null hachage ne fonctionne pas pour ce cas d'utilisation.

Le problème était la façon dont il gère la concaténation des valeurs null. Par exemple:

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

1Type113225  
<NULL>
2Type11751222 
3Type10651010 
4Type10651

Je pourrais utiliser fusionner ou de l'AFFAIRE états si je savais que le type; cependant, j'ai beaucoup de tables et je sais pas le type de l'avance de chaque colonne.

OriginalL'auteur brianray | 2013-01-29

23

Il est beaucoup plus élégant solution pour cela.

Dans Postgres, à l'aide de nom de table dans SELECT est permis, et elle est de type ROW. Si vous lancez ce type TEXT, il donne toutes les colonnes concaténées dans la chaîne qui est en fait JSON.

Avoir cela, vous pouvez obtenir md5 de toutes les colonnes comme suit:
```
SELECT md5(mytable::TEXT)
FROM mytable
```
Si vous voulez utiliser seulement certaines colonnes, utilisez ROW constructeur, et de le jeter à TEXT:
```
SELECT md5(ROW(col1, col2, col3)::TEXT)
FROM mytable
```
Une autre belle propriété à propos de cette solution est que md5 sera différente pour NULL vs chaîne vide.

Obligatoire SQLFiddle.

Très joli et élégant!
juste pour la prochaine personne - non, cela ne marche pas avec le redshift. redshift ne supporte pas la syntaxe (# SELECT md5(precipitation::TEXT) from precipitation; ERROR: column "precipitation" does not exist in precipitation)
redshift est basée sur la très vieille version de postgresql. Il fonctionne très bien sur postgresql 8.4+
Tout simplement magnifique.
Obtenez l'erreur suivante sur Postgresql 8.2.15 ERREUR: ne peut pas lancer de disque du type de texte

OriginalL'auteur mvp
3

Vous pouvez également utiliser quelque chose de similaire pour le mvp de la solution. Au lieu d'utiliser ROW() fonction qui n'est pas pris en charge par Amazon Redshift...

Opération non valide: LIGNE expression, implicite ou explicite, n'est pas pris en charge dans la liste des cibles;

Ma proposition est d'utiliser NVL2 et de la fonction CAST pour convertir différents types de colonnes de CHAR, aussi longtemps que ce type est compatible avec tous les Redshift types de données selon la la documentation. Ci-dessous, il est un exemple de la façon de réaliser null preuve MD5 dans Redshift.
```
SELECT md5(NVL2(col1,col1::char,''), 
           NVL2(col2,col2::char,''), 
           NVL2(col3,col3::char,''))
FROM mytable
```
Cela pourrait fonctionner sans remettre en deuxième NVL2 fonction argument de type char, mais il serait certainement échouer si vous essayez d'obtenir md5 à partir de la date de la colonne avec la valeur null.
J'espère que cela serait utile pour quelqu'un.

OriginalL'auteur najczuk
0

Avez-vous essayé d'utiliser CONCAT()? J'ai juste essayé dans mon PG 9.1 installer:
```
SELECT CONCAT('aaaa',1111,'bbbb');     => aaaa1111bbbb
SELECT CONCAT('aaaa',null,'bbbb');     => aaaabbbb
```
Par conséquent, vous pouvez essayer:
```
SELECT MD5(CONCAT(column1, column2, column3, column_n))    => md5_hash string here
```
Cette solution permettra de donner exactement le même md5 pour NULL et pour les cordes à vide - probablement pas ce que vous voulez.
Est-il un moyen de changer la accepté de répondre à mvp? Cette solution est beaucoup plus élégant.
Seuls les OP ne peut changer cela. Voir cette question connexe sur meta.DONC. Je suppose que vous pourriez ajouter une note renvoyant à la supérieure de réponse.
ok, la réponse sélectionnée va mvp; cependant, pour être honnête, je ne veux traiter nulle et vide chaîne de la même dans mon cas. Merci à tous les deux.

OriginalL'auteur Mark Anderson

Vous devez vous connecter pour publier un commentaire.