The prefix extension is a third-party extension that adds string prefix matching to PolarDB for PostgreSQL. A typical use case is telephony: given a phone number, find the operator whose prefix matches—specifically, the longest matching prefix in the table.
SELECT *
FROM prefixes
WHERE prefix @> '0123456789'
ORDER BY length(prefix) DESC
LIMIT 1;Prerequisites
The prefix extension requires a PolarDB for PostgreSQL cluster running PostgreSQL 14 with revision version 14.10.18.0 or later.
To check your revision version, run:
SELECT version();Enable the extension
CREATE EXTENSION prefix;Create a table and index
The extension introduces the prefix_range data type. Use it as the primary key of your prefixes table, then create a GiST index on that column. The GiST index enables fast prefix lookups—without it, every query requires a full table scan.
CREATE TABLE prefixes (
prefix prefix_range PRIMARY KEY,
name TEXT NOT NULL,
shortname TEXT,
status CHAR DEFAULT 'S',
CHECK( status IN ('S', 'R') )
);
COMMENT ON COLUMN prefixes.status IS 'S: - R: reserved';
INSERT INTO prefixes (prefix, name, shortname) VALUES
('010001','COLT TELECOMMUNICATIONS FRANCE','COLT'),
('010002','EQUANT France','EQFR'),
('010003','NUMERICABLE','NURC');
CREATE INDEX idx_prefix ON prefixes USING gist(prefix);Match a prefix
Use the @> operator to test whether a prefix_range value contains a given string:
SELECT '123'::prefix_range @> '123456';
?column?
----------
t
(1 row)
SELECT * FROM prefixes WHERE prefix @> '01000123';
prefix | name | shortname | status
--------+--------------------------------+-----------+--------
010001 | COLT TELECOMMUNICATIONS FRANCE | COLT | S
(1 row)Supported operators
The prefix extension supports the standard comparison operators <=, <, =, <>, >=, and >, which behave the same as their string counterparts. It also adds five prefix-specific operators: @> (contains), <@ (is contained by), && (overlaps), | (union), and & (intersection).
Comparison and containment operators
SELECT a, b,
a <= b AS "<=", a < b AS "<", a = b AS "=", a <> b AS "<>", a >= b AS ">=", a > b AS ">",
a @> b AS "@>", a <@ b AS "<@", a && b AS "&&"
FROM (SELECT a::prefix_range, b::prefix_range
FROM (VALUES('123', '123'),
('123', '124'),
('123', '123[4-5]'),
('123[4-5]', '123[2-7]'),
('123', '[2-3]')) AS t(a, b)) AS x;
a | b | <= | < | = | <> | >= | > | @> | <@ | &&
----------+----------+----+---+---+----+----+---+----+----+----
123 | 123 | t | f | t | f | t | f | t | t | t
123 | 124 | t | t | f | t | f | f | f | f | f
123 | 123[4-5] | t | t | f | t | f | f | t | f | t
123[4-5] | 123[2-7] | f | f | f | t | t | t | f | t | t
123 | [2-3] | t | t | f | t | f | f | f | f | f
(5 rows)Union and intersection operators
SELECT a, b, a | b AS UNION, a & b AS INTERSECT
FROM (SELECT a::prefix_range, b::prefix_range
FROM (VALUES('123', '123'),
('123', '124'),
('123', '123[4-5]'),
('123[4-5]', '123[2-7]'),
('123', '[2-3]')) AS t(a, b)) AS x;
a | b | union | intersect
----------+----------+----------+-----------
123 | 123 | 123 | 123
123 | 124 | 12[3-4] |
123 | 123[4-5] | 123 | 123[4-5]
123[4-5] | 123[2-7] | 123[2-7] | 123[4-5]
123 | [2-3] | [1-3] |
(5 rows)