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

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


The function expects text as parameter. Cast it:

SELECT request_id, md5(request_id::text)FROM RequestORDER 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;


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.


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 RequestORDER BY request_id--1;"c4ca4238a0b923820dcc509a6f75849b"--2;"c81e728d9d4c2f636f067f89cc14862c"--etc