到目前为止，答案仍无法解决您的问题：

The answers so far fail to address your question:

但是我想在查询中使用Like，％，_等，以便可以轻松地匹配
模式（不是全部8个字符）。

but I want use Like, % , _ etc in my query so that I can loosely match the pattern (that is not all 8 characters).

只要使用 Like 或 = 几乎没有什么区别您可以匹配整个字符串（并且字符串中没有通配符 ）。要使搜索模糊，您需要替换模式的一部分，而不仅仅是添加到模式中。

It makes hardly any difference whether you use LIKE or = as long as you match the whole string (and there are no wildcard character in your string). To make the search fuzzy, you need to replace part of the pattern, not just add to it.

例如，要匹配子列的后7个字符（而不是8个）：

For instance, to match on the last 7 (instead of 8) characters of subcolumn:

SELECT *
FROM   maintable m
WHERE  left(maincolumn, 8) LIKE 
       ( '%' || left((SELECT subcolumn FROM subtable WHERE subid = 2), 7));

我使用更简单的 left（）（由Postgres 9.1引入）。

您可以简化为：

I use the simpler left() (introduced with Postgres 9.1).
You could simplify this to:

SELECT *
FROM   maintable m
WHERE  left(maincolumn, 7) =
       (SELECT left(subcolumn,7) FROM subtable WHERE subid = 2);

但是如果您使用我在下面进一步提到的特殊索引，则不会，因为函数索引中的表达式必须完全匹配才能使用。

But you wouldn't if you use the special index I mention further down, because expressions in functional indexes have to matched precisely to be of use.

您可能对扩展名 pg_tgrm 。

You may be interested in the extension pg_tgrm.

在PostgreSQL 9.1中每个数据库运行一次：

In PostgreSQL 9.1 run once per database:

CREATE EXTENSION pg_tgrm;

两个原因：

• 它提供了 相似度运算符％ 。有了它，您可以构建一个智能的相似度搜索：

• It supplies the similarity operator %. With it you can build a smart similarity search:

--SELECT show_limit();
SELECT set_limit(0.5); -- adjust similarity limit for % operator

SELECT *
FROM maintable m
WHERE left(maincolumn, 8) %
      (SELECT subcolumn FROM subtable WHERE subid = 2);

CREATE INDEX maintable_maincol_tgrm_idx ON maintable
USING gist (left(maincolumn, 8) gist_trgm_ops);