postgresql: data type for md5 message digest?

Question

I want to use the MD5 message digest of some string as the primary key of a table. What datatype should I use for such a field? What select and insert statements should I write for the field?

Answer 1

The md5 hash as bytea will use only 16 bytes in instead of 32 for the hexa representation:

create table t (d bytea);
insert into t (d) values
    (digest('my_string', 'md5')),
    (decode(md5('my_string'), 'hex'));

Both forms above will work but to use the simpler digest function it is necessary to install the pgcrypto extension as superuser:

create extension pgcrypto;

Use the digest function or the combination of decode and md5 as above to search for a certain string:

select
    octet_length(d) ba_length,
    pg_column_size(d) ba_column,
    encode(d, 'hex') hex_representation,
    octet_length(encode(d, 'hex')) h_length,
    pg_column_size(encode(d, 'hex')) h_column
from t
where d = digest('my_string', 'md5')
;
 ba_length | ba_column |        hex_representation        | h_length | h_column 
-----------+-----------+----------------------------------+----------+----------
        16 |        17 | 3d212b21fad7bed63c1fb560c6a5c5d0 |       32 |       36
        16 |        17 | 3d212b21fad7bed63c1fb560c6a5c5d0 |       32 |       36

The pg_column_size value is the storage size. It is less than half for the bytea compared to the hexa representation.

Answer 2

bytea has a one byte overhead, but with padding to eight bytes this will result in significant wastage.

Instead, consider using the uuid type, which uses just 16 bytes. You'll have to use something like REPLACE(md5::text, '-', '') as md5 when selecting it, but that should be a quick operation.