Strings

MD5

Calculates the MD5 hash of a string — 32 hexadecimal characters.

Syntax

MD5(<text>)

The same text always gives the same hash, and different text almost certainly gives a different one. Handy for quickly comparing large texts, spotting whether a row has changed or making a short, stable key from several fields.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The hash of a string

SELECT MD5('hello') AS hash;

hash
5d41402abc4b2a76b9719d911017c592

Same text — same hash

SELECT MD5('Emma') = MD5('Emma') AS same,

MD5('Emma') = MD5('emma') AS different_case;

same	different_case
true	false

A fingerprint of every table row

SELECT name,

MD5(CONCAT_WS('|', name, department, salary)) AS fingerprint

FROM employees;

name	fingerprint
Emma	955dde73b3e3b6e4dec390ce6f57e831
Liam	8be374388162c439affdcc7e7f3747b6
Olivia	2c2d88d9b7eb90779e7219d9f95ea608
Noah	5ddac0a080c459d3a34eff666ef5f2b1
Sophia	10955739914543cdf5560890576e4205

If any of these columns changes, the fingerprint changes too.

Watch out

MD5 is no good for passwords: people learned to crack it long ago. Passwords are hashed with special algorithms, such as crypt() from the pgcrypto extension.

When joining fields into a fingerprint, put a separator between them: CONCAT('ab', 'c') and CONCAT('a', 'bc') give the same text and therefore the same hash. That's why the example uses CONCAT_WS('|', …).

NULL as input gives NULL, not the hash of an empty string.

Related functions

CONCAT RANDOM LENGTH