0

I need a query that will encode a TEXT column w SHA1. It is not for passwords, but used as a simple changed status check. I truncate at 40chars as it is a loose check.

This query part works in MS SQL SERVER:

RIGHT( CONVERT([varchar](45), HASHBYTES('SHA1', CAST(MY_XML as varchar(max))), 1) ,40) as HASH

A customer needs this to work in PostgreSQL.

I have looked at the ENCODE, DIGEST and other functions, but I can't seem to get the proper syntax.

These don't work:

DIGEST(CAST(MY_XML as varchar(max)),'SHA1')::varchar(40) as HASH
CAST(DIGEST(CAST(MY_XML as varchar),'SHA1') as varchar) as HASH
ENCODE(DIGEST(CAST(MY_XML as varchar(max)),'SHA1'),'hex') as HASH
ENCODE(DIGEST(MY_XML::text::bytea, 'sha1'), 'hex') as HASH

(last one sourced from How to calculate the real SHA1 of a text?)

I feel I'm close, but as the saying goes..

NEW2WEB
  • 503
  • 2
  • 8
  • 22
  • If you're just using this as a check for a changed value, does it matter if it's SHA-1? Surely any consistent hash calculation would do? – Tangentially Perpendicular Apr 03 '23 at 15:16
  • Unfortunately it does as this is checking on a remote system and our application DB uses SHA1 validation on our end already – NEW2WEB Apr 03 '23 at 15:18
  • What does "don't work" mean? – jjanes Apr 03 '23 at 15:19
  • 1) Define doesn't work. 2) For the examples you show you need to have the `pgcrypto` extension installed. Is that the case? 3) This `select encode(digest('this is test', 'sha1'), 'hex'); b6794b2000d94d348203d0279c2e7322b922cb16` works for me. – Adrian Klaver Apr 03 '23 at 15:20
  • I wasn't aware of the need for the `pgcrypto` extension. I'll investigate this. thank you – NEW2WEB Apr 03 '23 at 15:23
  • FWIW, any SHA1 hash is exactly 40 hex digits by definition, there's no effect to truncate it. – Bill Karwin Apr 03 '23 at 15:24
  • 1
    I also tested on my local PostgreSQL 14 instance (homebrew on MacOS), I just had to `CREATE EXTENSION 'pgcrypto';` then it worked. – Bill Karwin Apr 03 '23 at 15:25

0 Answers0