pgx_ulid

Overview

shared_preload_libraries = pgx_ulid is only required for gen_monotonic_ulid(); other functions work without it.

Version

Build

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

pig build pkg pgx_ulid         # build RPM / DEB packages

Install

You can install pgx_ulid 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 pgx_ulid;          # Install for current active PG version

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

dnf install -y pgx_ulid_18       # PG 18
dnf install -y pgx_ulid_17       # PG 17
dnf install -y pgx_ulid_16       # PG 16
dnf install -y pgx_ulid_15       # PG 15
dnf install -y pgx_ulid_14       # PG 14

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

Preload:

shared_preload_libraries = 'pgx_ulid';

Create Extension:

CREATE EXTENSION pgx_ulid;

Usage

Sources: README, releases

pgx_ulid provides a native ulid type, generators, and casts to and from timestamp and uuid. The README documents binary storage and monotonic ULID support. The pgext catalog tracks package and extension name pgx_ulid, version 0.2.3, for PostgreSQL 14-18.

Enable the extension

CREATE EXTENSION pgx_ulid;

Generate ULIDs

SELECT gen_ulid();
SELECT gen_monotonic_ulid();

gen_monotonic_ulid() needs:

shared_preload_libraries = 'pgx_ulid'

The README explicitly says this preload requirement only affects gen_monotonic_ulid(); the rest of the extension works without it.

Use ulid as a primary key

CREATE TABLE users (
  id ulid NOT NULL DEFAULT gen_ulid() PRIMARY KEY,
  name text NOT NULL
);

SELECT * FROM users
WHERE id = '01ARZ3NDEKTSV4RRFFQ69G5FAV';

Casts and range queries

ALTER TABLE users
ADD COLUMN created_at timestamp GENERATED ALWAYS AS (id::timestamp) STORED;

SELECT * FROM users
WHERE id BETWEEN '2023-09-15'::timestamp::ulid
            AND '2023-09-16'::timestamp::ulid;

The README also documents casts between ulid and uuid.

Caveats

• Monotonic ULIDs use shared memory plus an LWLock to keep the last generated value.
• The README notes that monotonic generation can theoretically overflow and raise an error, although it treats this as negligible in practice.
• The upstream README also shows CREATE EXTENSION ulid; this stub follows db/extension.csv, where the package and lead extension are both pgx_ulid.
• The GitHub release page lists v0.2.3 as latest and only labels it Release 0.2.3, with no separate user-facing release notes.