pgx_ulid

pgx_ulid : ulid type and methods

Overview

Packages

Source

pig build pkg pgx_ulid;		# 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 pgx_ulid;		# install via package name, for the active PG version

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

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pgx_ulid';

Create this extension with:

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.