In PostgreSQL, the md5() function computes the MD5 hash of the specific text, and outputs the result in hexadecimal.

We pass the text as an argument when calling the function.

Example

Here’s an example to demonstrate:

SELECT md5('Dog');

Result:

c935d187f0b998ef720390f85014ed1e

Here it is with some other values:

\x
SELECT 
    md5('Dog') AS "Dog",
    md5(' Dog ') AS " Dog ",
    md5('Cat') AS "Cat",
    md5('PostgreSQL') AS "PostgreSQL",
    md5('Pacific Ocean') AS "Pacific Ocean";

Result (using vertical output):

Dog           | c935d187f0b998ef720390f85014ed1e
 Dog          | 1b1cacd103da7827a17ae8e071ddc602
Cat           | fa3ebd6742c360b2d9652b7f78d9bd7d
PostgreSQL    | 399bd1ee587245ecac6f39beaa99886f
Pacific Ocean | 3e3b0e4c1d8d14efb313ca74f3ead4cb

Here, the first column has the same value that I passed in the first example, and we get the same output that we got in the first example.

White Space

We can see from the previous example that adding leading and trailing white space affected the result. If we don’t want to include leading or trailing white space, then we can use a function like trim():

SELECT 
    md5(' Dog ') AS "Untrimmed",
    md5(trim(' Dog ')) AS "Trimmed",
    md5('Dog') AS "Dog";

Result (using vertical output):

Untrimmed | 1b1cacd103da7827a17ae8e071ddc602
Trimmed   | c935d187f0b998ef720390f85014ed1e
Dog       | c935d187f0b998ef720390f85014ed1e

So we can see that the second and third columns return the same result. That’s because they’re effectively the same string; the second column has had all its leading and trailing white space trimmed, which results in the same string that we pass in the third column.

We can also use btrim(), rtrim() and ltrim() to trim white space as required.

Passing a Null Value

Passing a null value results in null being returned:

SELECT md5(null);

Result:

null