md5hash

md5hash : type for storing 128-bit binary data inline

Overview

Packages

Source

pig build pkg md5hash;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install md5hash;		# install via package name, for the active PG version

pig install md5hash -v 18;   # install for PG 18
pig install md5hash -v 17;   # install for PG 17
pig install md5hash -v 16;   # install for PG 16
pig install md5hash -v 15;   # install for PG 15
pig install md5hash -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION md5hash;

Usage

md5hash: native MD5 hash data type for PostgreSQL

The md5hash extension provides an efficient 128-bit data type for storing MD5 hashes in binary format (16 bytes) instead of text (32+ bytes).

CREATE EXTENSION md5hash;

CREATE TABLE test_table (
    id md5hash PRIMARY KEY
);

INSERT INTO test_table VALUES ('c4ca4238a0b923820dcc509a6f75849b');

SELECT * FROM test_table
WHERE id = 'c4ca4238a0b923820dcc509a6f75849b';

Operators

Standard comparison operators are supported: =, <>, <, >, <=, >=.

Index Support

Btree index operator class is included, enabling efficient lookups and primary key constraints on md5hash columns.

Storage Benefits

Compared to storing MD5 as text, the md5hash type uses approximately 60% of the storage space and provides faster indexed lookups.