HMAC

Use a secret defined by a hexadecimal string to return an HMAC (hash-based message authentication code).

HMAC(string, hex_string [, algorithm ])
string
Specify an input character string, or an expression that evaluates to a character string.
hex_string
Specify a hexadecimal string that provides a secret for running the function.
algorithm
Optionally, specify one of the following algorithms. The default is 2 (SHA-2).
Algorithm Mode Output Length (Bits) Output Type
0 MD5 128 VARCHAR(128)
1 SHA-1 160
2 SHA-2 256
3 SHA-2 512

The return type of this function is VARCHAR(128).

Examples

For example, using SHA-1 mode and two different "secret" values:
premdb=> select hmac(nickname,'f5e4d3c2b1a0',1) from team order by teamid limit 3;
                   hmac                   
------------------------------------------
 be4a4a1100473015d41a658ab01040e23f0cccbe
 56156fa4e1c30e890849323bef6ca23f272bbc3f
 068516ffb71a4c585cc16fce3e5afa418ac17016
(3 rows)

premdb=> select hmac(nickname,'d3c2b1a0',1) from team order by teamid limit 3;
                   hmac                   
------------------------------------------
 22957e439e9c79b6fa790f9665a44f5847a00cf1
 f40feb8474bb96832bf6469fd6df7832133a7cef
 f3ff8dcf59cfdc8b70607f45e2c1ba0e3213a500
(3 rows)
In this example, the same secrets are used with SHA-2 mode (with a length of 256 bits):
premdb=> select hmac(nickname,'f5e4d3c2b1a0',2) from team order by teamid limit 3;
                               hmac                               
------------------------------------------------------------------
 b28f48afa05c480db5f6ade36d6c79636ba0ad7b0cfd81b7f45de139da7927e8
 f7da1a52b3d6a0613a0ee654c3a02bd900cc15a0ac52d6ad5e4db84c68b92380
 5bc029cf8e0be0cadc026e01cfc88209bba7a24cb01eebed400cd29d323dfb3f
(3 rows)

premdb=> select hmac(nickname,'d3c2b1a0',2) from team order by teamid limit 3;
                               hmac                               
------------------------------------------------------------------
 403ea5b09d9f29a145a2373806b41d8f3651f7e2ac0e6275cb01d7cb93247d15
 052f4b11b93de81b0707f1a1fa1c9d991a64c704c2bc5c0a6e068c5aab8b0c35
 7f06ac46b2c99d398a5624d7cece79a4517e87cc491bf837cc81929b942993e6
(3 rows)
This example uses SHA-2 mode (with a length of 512 bits):
premdb=# select hmac(nickname,'d3c2b1a0',3) from team order by teamid limit 3;
                                                               hmac                                                               
----------------------------------------------------------------------------------------------------------------------------------
 5a724fdd70367f56a2d290f6dbbd729782821846d7e4cfa7c99606b16446fc76b8c3012fe6bfc6ae43b0c65ca25193a5e61b184cd9b3a678de6b6d347571615d
 c23caf7fd7029b3a5d14ecfd38947b87a8a0dbc51bbfaa10c72dd5138f97b201aa171b425f24999a1ab046c83726cca67a4bfb93fa26ca17f681a71ac1d2afaa
 16a5c1e96d75b3f3d99df35530ba13877cd29accae35799f3c6a1b2779679857423085553bb41ff904b2b27ccfe56a9dc2e98fcf4519a9eef7129eb243a89eec
(3 rows)
This example uses MD5 mode:
premdb=# select hmac(nickname,'d3c2b1a0',0) from team order by teamid limit 3;
               hmac               
----------------------------------
 d84f1cf1380d1e2d44c330096e141ce6
 2c7e526250cf6a584bbcdd269b8bc620
 59a11ef3c6f4f86a8ae40c503653938c
(3 rows)