PostgreSQL如何统计以指定前缀开头且含指定连字符数的列条目

要实现以指定前缀开头且包含指定数量连字符的统计，核心是完成两步校验：匹配目标前缀、精确统计连字符总数。原SQL的LIKE条件逻辑错误，导致无法匹配数据，以下是两种可行方案：

方案一：用字符串函数统计连字符数量 #

利用length()和replace()计算ref中连字符的总个数，结合前缀匹配：

SELECT COUNT(*)
FROM messages_dev
WHERE ref LIKE '2023-08-04T09:00:32.822+00:00%'
  AND (LENGTH(ref) - LENGTH(REPLACE(ref, '-', ''))) = 3;

• 逻辑说明：LENGTH(ref) - LENGTH(REPLACE(ref, '-', ''))会算出ref中-的总个数，等于指定的3即符合条件；LIKE '前缀%'确保字符串以目标前缀开头。

方案二：用正则表达式匹配 #

如果前缀包含.、+这类正则特殊字符，用正则匹配更精准：

SELECT COUNT(*)
FROM messages_dev
WHERE ref ~ '^2023-08-04T09:00:32\.822\+00:00.*'
  AND (SELECT COUNT(*) FROM regexp_matches(ref, '-', 'g')) = 3;

• 逻辑说明：^前缀.*的正则规则确保字符串以指定前缀开头；regexp_matches(ref, '-', 'g')提取所有-，统计其数量等于3即符合条件，注意需转义.和+。

动态变量版本（适配参数输入） #

如果前缀和连字符数量是动态变量，可写成带参数的形式：

SELECT COUNT(*) INTO result
FROM messages_dev
WHERE ref LIKE input || '%'
  AND (LENGTH(ref) - LENGTH(REPLACE(ref, '-', ''))) = hyphens;

这里input是你的前缀变量，hyphens是指定的连字符数量（比如3）。

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