pg_lexo

Overview

Release/package 0.6.1 ships SQL/control version 0.6.0. Objects use the chosen installation schema; the old lexo schema was removed. superuser=false is not trusted=true. PGSTY patches lexo_next for native-type ordering and empty tables, and lexo_rebalance to preserve order independently of text collation.

Version

Build

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

pig build pkg pg_lexo         # build RPM / DEB packages

Install

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

pig ext install -y pg_lexo -v 18  # PG 18
pig ext install -y pg_lexo -v 17  # PG 17
pig ext install -y pg_lexo -v 16  # PG 16

dnf install -y pg_lexo_18       # PG 18
dnf install -y pg_lexo_17       # PG 17
dnf install -y pg_lexo_16       # PG 16

apt install -y postgresql-18-pg-lexo   # PG 18
apt install -y postgresql-17-pg-lexo   # PG 17
apt install -y postgresql-16-pg-lexo   # PG 16

Create Extension:

CREATE EXTENSION pg_lexo;

Usage

Sources:

• Official README
• Version 0.6 SQL notes
• Public function implementation

pg_lexo 0.6.0 provides a Base62 lexo type for stable user-controlled ordering. It generates short values before, after, or between existing positions, allowing an item to move without renumbering every row. The type has comparison operators plus default B-tree and hash operator classes.

Core Workflow

CREATE EXTENSION pg_lexo;

CREATE TABLE items (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    position lexo NOT NULL UNIQUE DEFERRABLE
);

INSERT INTO items (title, position)
VALUES ('first', lexo_first());

INSERT INTO items (title, position)
SELECT 'last', lexo_after(position)
FROM items ORDER BY position DESC LIMIT 1;

INSERT INTO items (title, position)
VALUES ('middle', lexo_between('H'::lexo, 'I'::lexo));

SELECT * FROM items ORDER BY position;

lexo_first() starts at H. lexo_after(lexo), lexo_before(lexo), and lexo_between(lexo, lexo) generate neighboring positions; either argument to lexo_between may be NULL to represent an open end. Inputs contain only 0-9, A-Z, and a-z, whose ASCII order matches the type’s ordering.

Table Helpers

lexo_next(table, position_column, key_column, key_value) finds the current maximum and returns the next position, optionally within a group. lexo_add_column(table, column) adds a lexo column. lexo_rebalance(table, position_column, key_column, key_value) rewrites matching rows with evenly distributed positions and returns the number updated.

The helpers issue dynamic SQL and require the caller to have the corresponding table privileges. Rebalancing updates every selected row and should run with application writers coordinated. If positions have a unique constraint, use a deferrable constraint and defer it within the rebalancing transaction to allow intermediate values to overlap. Position generation itself does not reserve a value: concurrent sessions can derive the same position, so enforce a suitable unique constraint and retry or serialize conflicting moves. When no Base62 value exists between adjacent positions (or before the minimum), rebalance first.

Version 0.6 Boundary

Version 0.6 removed the dedicated lexo schema. The former lexo.lexorank type became lexo, and schema-qualified functions such as lexo.first() became names such as lexo_first(). Older examples are incompatible until migrated. Release/package 0.6.1 retains SQL/control version 0.6.0 and targets PostgreSQL 16–18 through pgrx 0.16.1. Pigsty packages patch lexo_next to use native ordering instead of the unavailable max(lexo) aggregate, and keep lexo_rebalance ordering independent of text collation.