prefix
Prefix Range module for PostgreSQL
Repository
dimitri/prefix
https://github.com/dimitri/prefix
Source
prefix-1.2.10.tar.gz
prefix-1.2.10.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_prefix | 1.2.10 | TYPE | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 3500 | prefix | No | Yes | No | Yes | No | Yes | - |
| Related | semver ltree citext pg_trgm unit pgpdf pglite_fusion md5hash |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 1.2.10 | 1817161514 | pg_prefix | - |
| RPM | PGDG | 1.2.10 | 1817161514 | prefix_$v | - |
| DEB | PGDG | 1.2.10 | 1817161514 | postgresql-$v-prefix | - |
Build
You can build the RPM packages for pg_prefix using pig build:
pig build pkg pg_prefix # build RPM packages
Install
You can install pg_prefix directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pg_prefix; # Install for current active PG version
pig ext install -y pg_prefix -v 18 # PG 18
pig ext install -y pg_prefix -v 17 # PG 17
pig ext install -y pg_prefix -v 16 # PG 16
pig ext install -y pg_prefix -v 15 # PG 15
pig ext install -y pg_prefix -v 14 # PG 14
dnf install -y prefix_18 # PG 18
dnf install -y prefix_17 # PG 17
dnf install -y prefix_16 # PG 16
dnf install -y prefix_15 # PG 15
dnf install -y prefix_14 # PG 14
apt install -y postgresql-18-prefix # PG 18
apt install -y postgresql-17-prefix # PG 17
apt install -y postgresql-16-prefix # PG 16
apt install -y postgresql-15-prefix # PG 15
apt install -y postgresql-14-prefix # PG 14
Create Extension:
CREATE EXTENSION prefix;
Usage
The prefix extension provides a prefix_range data type for efficient prefix matching, particularly useful for telephony call routing.
Data Type
Create prefix_range values using the constructor or text casting:
CREATE EXTENSION prefix;
SELECT prefix_range('123'); -- 123
SELECT prefix_range('123', '4', '5'); -- 123[4-5]
SELECT '123'::prefix_range; -- 123
Operators
| Operator | Description |
|---|---|
@> | Contains (prefix contains value) |
<@ | Is contained by |
&& | Overlaps |
| | Union |
& | Intersection |
=, <>, <, >, <=, >= | Comparison |
Examples
-- Find the longest matching prefix for a phone number
SELECT * FROM prefixes
WHERE prefix @> '0123456789'
ORDER BY length(prefix) DESC
LIMIT 1;
-- Containment check
SELECT '123'::prefix_range @> '123456'; -- true
-- Intersection
SELECT '123[4-5]' & '123[2-7]'; -- 123[4-5]
-- Union
SELECT '123' | '124'; -- 12[3-4]
Index Support
Create a GiST index for efficient prefix lookups:
CREATE INDEX idx_prefix ON prefixes USING gist(prefix);
The index accelerates @>, <@, &&, and = operators.
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.