正则表达式匹配 https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP几乎从 7.1 版开始就一直存在于 Postgres 中。使用这些运营商 https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-TABLE:
~ !~ ~* !~*
有关概述,请参阅:
- PostgreSQL 中使用 LIKE、SIMILAR TO 或正则表达式进行模式匹配 https://dba.stackexchange.com/q/10694/3684
你的情况的要点似乎是不允许更多的点:
SELECT *
FROM tbl
WHERE version LIKE '14.1.%' -- for performance
AND version ~ '^14\.1\.[^.]+$'; -- for correct result
db<>fiddle
Old sqlfiddle http://sqlfiddle.com/#!17/0a28f/420
The LIKE
表达式是多余的,但即使没有索引,它也会显着提高性能。当然,你应该有一个索引。
The LIKE
表达式可以使用基本的text_pattern_ops
索引,而正则表达式不能,至少在 Postgres 8.4 中是这样。
或者从 Postgres 9.1 开始使用 COLLATE "C"。看:
- text_pattern_ops 和 COLLATE "C" 之间有区别吗? https://dba.stackexchange.com/a/291250/3684
- PostgreSQL LIKE 查询性能变化 https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations/13452528#13452528
[^.]
正则表达式模式中的字符类不包括点 (.
)。所以允许更多的字符,只是不再有点。
表现
要为这个特定查询挤出最佳性能,您可以添加一个专门的索引:
CREATE INDEX tbl_special_idx ON tbl
((length(version) - length(replace(version, '.', ''))), version text_pattern_ops);
并使用匹配查询,与上面相同,只需将最后一行替换为:
AND length(version) - length(replace(version, '.', '')) = 2
db<>fiddle
Old sqlfiddle http://sqlfiddle.com/#!17/8fe99b/1