pg_lexo

pg_lexo : Lexicographic position type for inserting and reordering list items

Overview

Packages

Source

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

pig install pg_lexo -v 18;   # install for PG 18
pig install pg_lexo -v 17;   # install for PG 17
pig install pg_lexo -v 16;   # install for PG 16

Create this extension with:

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.