Skip to content

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).

AlgorithmModeOutput Length (Bits)Output Type
1SHA-1160VARCHAR(128)
2SHA-2256

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:

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)

Parent topic:String Functions