prefix(前缀匹配)

prefixPolarDB PostgreSQL版支持的一款第三方插件,用于字符串前缀匹配。

前提条件

支持的PolarDB PostgreSQL版的版本如下:

PostgreSQL 14(内核小版本14.10.18.0及以上)。

说明

您可通过如下语句查看PolarDB PostgreSQL版的内核小版本号:

SELECT version();

背景信息

字符串前缀匹配是一个常见的业务功能。例如,在电话应用中,运营商可能希望在用户输入电话号码前缀时,能够即时检索出满足前缀匹配的电话号码的完整列表。PolarDB PostgreSQL版支持prefix插件用于实现字符串前缀匹配的功能。以下是一个最长前缀匹配的示例:

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

使用方法

创建插件

CREATE EXTENSION prefix;

进行前缀匹配

  1. 准备基础数据,创建表和索引。

    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);
  2. 执行如下语句,进行前缀匹配。

    • 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)

可使用的操作符

prefix插件提供了一系列操作符,包括常规的<=<=<>>=>操作符,它们的含义与字符串比较操作符相同。此外,还有@>(包含)、<@(被包含)、&&(重叠)、|(并集)以及&(交集)。

  • 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)