md5() works with literal, but not with column data

Question

While testing out PostgreSQL's md5() function I noticed very bizarre behavior:

Works as expected

SELECT md5('abc')
--"900150983cd24fb0d6963f7d28e17f72"

But using the md5() function in a query:

SELECT request_id, md5(request_id)
FROM Request
ORDER BY request_id

results in this error:

ERROR:  function md5(integer) does not exist
LINE 1: SELECT request_id, md5(request_id)
                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function md5(integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 20

How can the function not exist if it worked in the first query? What am I doing wrong; what is the correct way to use md5() in a SELECT query?

Answer 1

The function expects text as parameter. Cast it:

SELECT request_id, md5(request_id::text)
FROM Request
ORDER BY request_id

A function named md5 accepting an integer parameter does not exist but you can create it:

create function md5(integer)
returns text as $$

select md5($1::text);

$$ language sql immutable;

Then there will be 3 signatures for md5:

=> \df md5
                          List of functions
   Schema   | Name | Result data type | Argument data types |  Type  
------------+------+------------------+---------------------+--------
 pg_catalog | md5  | text             | bytea               | normal
 pg_catalog | md5  | text             | text                | normal
 public     | md5  | text             | integer             | normal

As pointed in the comments to this answer the md5 hash of the integer's text representation may not be what you want. To have the hash of the binary the md5 signature accepting a bytea parameter should be used:

select md5(('\x' || right('0000000' || to_hex(200), 8))::bytea);
               md5                
----------------------------------
 b7b436d004c1cc0501bee9e296d2eaa4

And replace the previously created function:

create or replace function md5(integer)
returns text as $$

select md5(('\x' || right('0000000' || to_hex($1), 8))::bytea);

$$ language sql immutable;

Answer 2

In general it doesn't make much sense to take the md5 of an integer. It seems likely that you trying to obscure a sequence so it appears semi-random in order. If so, there's a much better way:

Use the pseudo_encrypt function listed on the PostgreSQL wiki. It's way saner than trying to take the md5 of an integer then (presumably) truncate it.

The above does not provide strong cryptographic randomness, but neither does your approach. If you need your request IDs to be genuinely unpredictable for security reasons rather than just non-obvious at a casual glance you should be using a strong cryptographic random numbers generator and be prepared to cope with duplicates using time windows, etc.

Answer 3

The error is a little misleading; the md5() function exists, just not for working on integers. Use an embedded CAST() function to convert the integer field into text, and it will work:

SELECT request_id, md5(CAST(request_id AS TEXT))
FROM Request
ORDER BY request_id

--1;"c4ca4238a0b923820dcc509a6f75849b"
--2;"c81e728d9d4c2f636f067f89cc14862c"
--etc