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

md5, postgresql

Solution

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;

Problem

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?

Original source