MD5 is a built-in function in Postgres that takes a string as an argument and converts it into a 32 characters text string. It retrieves the resultant value in the hexadecimal. It is commonly used to fulfill security purposes. MD5 accepts only string values; passing an integer will result in an error.

This blog post will demonstrate the working of the MD5 function via practical examples. So, let’s get started.

How to Use MD5 Function in PostgreSQL?

MD5 is a cryptographic/encrypted hash function that generates a 128-bit hash value. The syntax of the MD5 function will look like this:

MD5(string_val);

The above snippet shows that the MD5 function accepts a single string argument “string_val”. The string_val represents a string to be converted into a 32-characters text string.

Note: When creating a user in Postgres, we can use MD5 to create an encrypted password for the newly created users.

Example 1: How Does the MD5 Function Work in Postgres?

Let’s pass a string value as an argument to the targeted function and see how it works:

SELECT MD5('Welcome to commandprompt.com');

The output verifies that the MD5 function successfully converted the given string into the 32-character text string.

Example 2: How Does the MD5 Function Deals With Integers?

In this example, we are going to pass an integer value to the MD5 function to see how it works in this particular situation:

SELECT MD5(512);

The output proves that an “argument types” error occurs when we pass an integer value to the MD5 function.

Example 3: How to Use MD5 Function on Table’s Data?

Let’s create a sample table named “user_detials” with two columns: user_name and account_num:

CREATE TABLE user_details(
user_name TEXT,
account_num TEXT
);

A table named user_details is created. In the newly created table, we can insert as many rows/records as we want:

INSERT INTO user_details(user_name, account_num)
VALUES ('Joe', 'xyz123abc'),
('John', 'avc1230987898'),
('Seth', 'xyz1231346712'),
('Joseph', '1234123980112'),
('Mike', 'xyz1232349801');

Now let’s utilize the MD5 function to convert the values of the “account_num” column into 32-character text strings:

SELECT user_name, account_num, MD5(account_num)
FROM user_details;

In the above snippet, we utilized the SELECT statement to fetch the user_name and account_num columns of the user_details table. Moreover, the MD5 function is used to convert the values of the account_num column to the 32 characters TEXT strings:

This is how you can convert a table’s column into a 32-character text string.

Conclusion

PostgreSQL provides a built-in function named MD5 that accepts a string as an argument and converts it into a 32 characters text string. It retrieves the resultant value in the hexadecimal. It is commonly used to fulfill security purposes. MD5 accepts only string values; passing an integer will result in an error. This blog post considered various examples to explain the working of the Postgres MD5 function.