PostgreSQL中如何查询所有以指定数字为最左前缀的ID

首先回应核心疑问：不需要强制把ID列整体修改为字符串类型，你既可以通过临时类型转换做字符串前缀匹配，也可以通过数值范围计算实现需求，两种方案的具体实现如下：

方案1：字符串前缀匹配（推荐，逻辑最直观） #

前缀匹配本质是字符串规则匹配，查询时把ID临时转为文本做LIKE前缀判断即可，不需要修改表字段的原有类型。
假设你的业务表名为biz_table，存储ID的字段为id，示例SQL如下：

WITH initial_root_ids AS (
    -- 此处替换为你实际查询初始ID的SQL逻辑，示例为直接传入指定ID集合
    SELECT unnest(ARRAY[1,2,3,4]) AS root_id
)
SELECT
    r.root_id,
    array_agg(t.id) AS matched_ids
FROM initial_root_ids r
LEFT JOIN biz_table t
    ON t.id::text LIKE (r.root_id::text || '%')
    -- 如果不需要把初始ID本身计入匹配结果，放开下面的注释即可
    -- AND t.id != r.root_id
GROUP BY r.root_id;

性能优化 #

如果表数据量较大，可以给ID列创建支持前缀匹配的表达式索引，查询时可以直接走索引避免全表扫描：

CREATE INDEX idx_biz_table_id_prefix ON biz_table ( (id::text) varchar_pattern_ops );

这个方案适配所有ID场景，不管ID是数值类型、定长字符串类型、甚至包含非数字字符都可以正常工作，是优先选择的实现方式。

方案2：数值范围匹配（无需类型转换） #

如果你的ID是纯数值类型、不存在前导零的情况，也可以通过数学计算划定匹配范围，完全不需要做类型转换：
对于任意初始IDroot_id，所有以它为最左前缀的ID，一定落在区间 [root_id * 10^k, (root_id + 1)*10^k - 1] 内，其中k为正整数，取值从1到表中ID的最大位数即可。
示例SQL如下：

WITH initial_root_ids AS (
    SELECT unnest(ARRAY[1,2,3,4]) AS root_id
),
id_meta AS (
    -- 计算当前表中ID的最大位数，避免无效范围计算
    SELECT length(max(id)::text) AS max_digit_count FROM biz_table
)
SELECT
    r.root_id,
    array_agg(DISTINCT t.id) AS matched_ids
FROM initial_root_ids r
CROSS JOIN generate_series(1, (SELECT max_digit_count FROM id_meta)) AS k
LEFT JOIN biz_table t
    ON t.id BETWEEN r.root_id * (10^k) AND (r.root_id + 1) * (10^k) - 1
    -- 不需要匹配初始ID本身时放开下面注释
    -- AND t.id != r.root_id
GROUP BY r.root_id;

这个方案的优势是全程做数值范围判断，如果ID字段本身建有普通B树索引，范围查询可以直接复用索引，不需要额外建表达式索引；缺点是逻辑不如字符串方案直观，遇到带前导零的字符串ID、非纯数字ID时会完全失效。

内容的提问来源于stack exchange，提问作者aDev