PolarDB:prefix

Prerequisites

This extension is supported by PolarDB for PostgreSQL (Compatible with Oracle) clusters of the following version:

PolarDB for PostgreSQL (Compatible with Oracle) 2.0 whose revision version is 2.0.14.18.0 or later

SHOW polar_version;

Background information

The string prefix matching feature is a common business feature. For example, in a telephony application, an operator may want you to retrieve a complete list of telephone numbers that match a specified telephone number prefix when you enter the prefix. PolarDB for PostgreSQL (Compatible with Oracle) allows you to use the prefix extension to implement the string prefix matching feature. The following example describes how to match the longest prefix:

SELECT *
    FROM prefixes
    WHERE prefix @> '0123456789'
ORDER BY length(prefix) DESC
    LIMIT 1;

Use the prefix extension

Create the prefix extension

CREATE EXTENSION prefix;

Create tables and indexes

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 specified prefix

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 provides a series of operators such as the following regular operators: <=, <,=, <>, >=, and >. The operators have the same meaning as string comparison operators. The extension also provides the following operators: @> (include), <@ (included), && (overlap), | (union), and & (intersection).

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)

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)