Postgresql：两列之间的匹配模式

本文介绍了Postgresql：两列之间的匹配模式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两列，分别是 Main 和 Sub 。 （它们可以在同一张表中，也可以不在同一张表中。）

I have two columns say Main and Sub. (they can be of same table or not).

Main 是长度为20且 Sub 是长度为8的varchar。 Sub 是始终的子集c $ c> Main ，它是 Main 的后8个字符。

Main is varchar of length 20 and Sub is varchar of length 8. Sub is always subset of Main and it is last 8 characters of Main.

I可以使用 substr（ Main，13,8）

查询来成功设计一个与模式匹配的查询：

Query:

select * from "MainTable" where substr("MainColumn",13,8) LIKE ( select "SubColumn" From "SubTable" Where "SubId"=1043);

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

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

问题是我该怎么做。？！

Question is how can i do that.?!

我知道下面的查询是完全错误，但我想实现这样的目标，

I know that the query below is COMPLETELY WRONG but I want to achieve something like this,

Select * from "MainTable" Where "MainColumn" Like '%' Select "SubColumn" From "SubTable" Where "SubId"=2'

推荐答案

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

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

它提供 索引支持 同时适用于 Like 和％

如果读取性能比写入性能更重要，建议您创建一个功能性 GIN或GiST索引如下：

If read performance is more important than write performance, I suggest you create a functional GIN or GiST index like this:

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

此索引支持任一查询。请注意，写入操作会带来一些成本。 此相关答案中类似情况的快速基准。

This index supports either query. Be aware that it comes with some cost for write operations. A quick benchmark for a similar case in this related answer.