md5hash

Overview

Version

Build

You can build the RPM / DEB packages for md5hash using pig build:

pig build pkg md5hash         # build RPM / DEB packages

Install

You can install md5hash directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install md5hash;          # Install for current active PG version

pig ext install -y md5hash -v 18  # PG 18
pig ext install -y md5hash -v 17  # PG 17
pig ext install -y md5hash -v 16  # PG 16
pig ext install -y md5hash -v 15  # PG 15
pig ext install -y md5hash -v 14  # PG 14

dnf install -y md5hash_18       # PG 18
dnf install -y md5hash_17       # PG 17
dnf install -y md5hash_16       # PG 16
dnf install -y md5hash_15       # PG 15
dnf install -y md5hash_14       # PG 14

apt install -y postgresql-18-md5hash   # PG 18
apt install -y postgresql-17-md5hash   # PG 17
apt install -y postgresql-16-md5hash   # PG 16
apt install -y postgresql-15-md5hash   # PG 15
apt install -y postgresql-14-md5hash   # PG 14

Create Extension:

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.