~是正则表达式运算符，并具有其隐含的功能。可以指定全范围的正则表达式通配符和限定符;有关详细信息，请参阅文档。它当然比LIKE更强大，应该在需要这种能力时使用，但它们的用途不同。

概述

LIKE、SIMILAR TO和~是基本的pattern matching operators in PostgreSQL。
如果可以的话，用**LIKE（~~），它是最快和最简单的。
如果不能，使用正则表达式（~），它更强大。
从不使用SIMILAR TO。这毫无意义见下文。
安装additional module pg_trgm会添加高级索引选项和相似性运算符%。
此外，还有text search，拥有自己的基础设施和@@**运营商（以及其他）。
索引支持对这些操作符中的每一个都是可用的-程度不同。它经常胜过其他选项的表现。但在细节上有很大的回旋余地，即使有索引。

索引支持

• 没有 * pg_trgm**，只有 * 左锚定 * 搜索模式的索引支持。如果您的数据库集群使用非C语言环境（典型情况）运行，则需要一个索引with a special operator class，例如text_pattern_ops或varchar_pattern_ops。它也支持基本的左锚定正则表达式。示例如下：

CREATE TABLE tbl(string text);

INSERT INTO  tbl(string)
SELECT x::text FROM generate_series(1, 10000) x;

CREATE INDEX tbl_string_text_pattern_idx ON tbl(string text_pattern_ops);

SELECT * FROM tbl WHERE string ~ '^1234';  -- left anchored pattern

字符串

• db<>fiddle here*
• 安装 * pg_trgm**后，GIN或GiST索引可以使用运算符类gist_trgm_ops或gin_trgm_ops。这些索引支持 anyLIKE表达式，而不仅仅是左锚定。并且，引用手册：

从PostgreSQL 9.3开始，这些索引类型也支持正则表达式匹配的索引搜索。
详细信息：

• PostgreSQL LIKE查询性能变化
  **SIMILAR TO**是一个非常奇怪的构造。PostgreSQL只实现了它，因为它是在SQL标准的早期版本中定义的。在内部，每个SIMILAR TO表达式都用正则表达式重写。因此，对于任何给定的SIMILAR TO表达式，至少有一个正则表达式执行相同的工作 * 更快 *。我 * 从不 * 使用SIMILAR TO。

进一步阅读：

• Pattern matching with LIKE, SIMILAR TO or regular expressions的

LIKE没有什么问题，而且，在我看来，没有理由支持~。恰恰相反。LIKE是SQL标准的。SIMILAR TO也是如此，但它没有得到广泛的支持。PostgreSQL的~ operator（或 *posix正则表达式匹配运算符 ）不是SQL标准。
出于这个原因，我更喜欢使用LIKE，它的表达能力足够，我只使用~时，我需要完整的正则表达式的力量。如果我需要移植数据库，那就少了一件伤害。当LIKE不够强大时，我倾向于使用SIMILAR TO，但是在Erwin的评论之后，我想我会停止这样做，当LIKE不能完成这项工作时，我会使用~。
此外，PostgreSQL可以使用b树索引进行前缀搜索（例如LIKE 'TEST%'），如果数据库位于C区域或索引具有text_pattern_ops，则使用LIKE或SIMILAR TO。与我之前写的相反，Pg也可以为左锚定的posix正则表达式使用这样的索引，它只需要一个显式的'^TEST.'，所以正则表达式只能从开始匹配。我之前的文章错误地指出~不能使用索引进行前缀搜索。在消除了这种差异之后，真正的问题在于您是否希望在可能的情况下坚持使用符合标准的特性。
查看此演示SQLFiddle;注意不同的执行计划。注意~ '1234.*'和~ '^1234.*'之间的区别。
给定样本数据：

create table test (
   blah text
);
insert into test (blah)  select x::text from generate_series(1,10000) x;
create index test_blah_txtpat_idx ON test(blah text_pattern_ops);

字符串
注意，~使用seqscan，即使它实际上更昂贵（由于enable_seqscan而人为地如此），因为它没有替代方案，而LIKE使用索引。但是，带有左锚点的校正~也使用索引：

regress=# SET enable_seqscan = 'f';
SET
regress=# explain select 1 from test where blah ~ '12.*';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Seq Scan on test  (cost=10000000000.00..10000000118.69 rows=2122 width=0)
   Filter: (blah ~ '12.*'::text)
(2 rows)
regress=# explain select 1 from test where blah like '12%';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=4.55..46.76 rows=29 width=0)
   Filter: (blah ~~ '12%'::text)
   ->  Bitmap Index Scan on test_blah_txtpat_idx  (cost=0.00..4.54 rows=29 width=0)
         Index Cond: ((blah ~>=~ '12'::text) AND (blah ~<~ '13'::text))
(4 rows)
regress=# explain select 1 from test where blah ~ '^12.*';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=5.28..51.53 rows=101 width=0)
   Filter: (blah ~ '^12.*'::text)
   ->  Bitmap Index Scan on test_blah_txtpat_idx  (cost=0.00..5.25 rows=100 width=0)
         Index Cond: ((blah ~>=~ '12'::text) AND (blah ~<~ '13'::text))
(4 rows)

型

Like只是匹配字符串的开头、结尾或中间的一部分，而tilt（~）匹配正则表达式
为了进一步解释，让我们创建一个表并插入一些值

# create table users(id serial primary key, name character varying);

字符串
现在让我们在表中插入一些值

# insert into users (name) VALUES ('Alex'), ('Jon Snow'), ('Christopher'), ('Arya'),('Sandip Debnath'), ('Lakshmi'),('alex@gmail.com'),('@sandip5004'), ('lakshmi@gmail.com');

型
现在你的表应该看起来像这样

id |       name        
----+-------------------
  1 | Alex
  2 | Jon Snow
  3 | Christopher
  4 | Arya
  5 | Sandip Debnath
  6 | Lakshmi
  7 | alex@gmail.com
  8 | lakshmi@gmail.com
  9 | @sandip5004

型

Case LIKE

# select * from users where name like 'A%';
 id | name 
----+------
  1 | Alex
  4 | Arya
(2 rows)

型
正如你所看到的，'A%'只会得到名字以大写字母A开头的值。

# select * from users where name like '%a%';
 id |       name        
----+-------------------
  4 | Arya
  5 | Sandip Debnath
  6 | Lakshmi
  7 | alex@gmail.com
  8 | lakshmi@gmail.com

型
正如你所看到的，'%a%'只会得到那些名字中间有a的值。

# select * from users where name like '%a';

 id | name 
----+------
  4 | Arya

型
正如你所看到的，'%a'只会得到以a结尾的值。

Case ~（tilt）

# select * from users where name ~* 't';
 id |      name      
----+----------------
  3 | Christopher
  5 | Sandip Debnath

型
正如你所看到的，name ~* 't'只会得到名字为t的值。~表示区分大小写，~* 表示不区分大小写，因此

# select * from users where name ~ 'T';
 id | name 
----+------
(0 rows)

型
由于T与任何条目都不匹配，因此上述查询给我们0行
现在让我们考虑一个情况，我们只需要获取电子邮件ID，我们不知道邮件ID有什么，但我们知道电子邮件的模式，即会有一些字母或数字或_或。或者-然后是@然后是更多的字母或数字或者-然后。然后com或in或orgetc，我们可以使用正则表达式创建模式。
现在让我们尝试使用正则表达式获取结果

# select * from users where name ~* '[a-z0-9\.\-\_]+@[a-z0-9\-]+\.[a-z]{2,5}';
 id |       name        
----+-------------------
  7 | alex@gmail.com
  8 | lakshmi@gmail.com

型
类似地，我们可以获取一些中间有空格的名称

#select * from users where name ~* '[a-z]+\s[a-z]+';
 id |      name      
----+----------------
  2 | Jon Snow
  5 | Sandip Debnath

型
[a-z]+意味着可以有从a到z的任何字母，+意味着它可能出现1次或更多次，\s意味着在那之后，中间将有一个空格，然后又是一组可以出现1次或更多次的字母。
希望这些详细的分析能有所帮助。

另一方面，The ~~ operator is equivalent to LIKE .~将使用POSIX正则表达式进行匹配。

我刚刚做了一个快速而简单的基准测试，以查看两个操作符 * 在不涉及索引 * 时的性能差异：

postgres=# \timing
Timing is on.
postgres=# SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text LIKE '%5%') AS x;
  count
─────────
 5217031
(1 row)

Time: 5631.662 ms
postgres=# SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text ~ '5') AS x;
  count
─────────
 5217031
(1 row)

Time: 10612.406 ms

字符串
在本例中，LIKE运算符的速度几乎是~运算符的两倍。因此，如果速度是关键，我会倾向于LIKE，但要注意不要过早优化。~为您提供了更大的灵活性。
对于那些有兴趣的人，这里是针对上述查询的EXPLAIN计划：

postgres=# EXPLAIN ANALYZE SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text LIKE '%5%') AS x;
                                                              QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Aggregate  (cost=20.00..20.01 rows=1 width=0) (actual time=9967.748..9967.749 rows=1 loops=1)
   ->  Function Scan on generate_series x  (cost=0.00..17.50 rows=1000 width=0) (actual time=1732.084..7404.755 rows=5217031 loops=1)
         Filter: ((val)::text ~~ '%5%'::text)
         Rows Removed by Filter: 4782969
 Total runtime: 9997.587 ms
(5 rows)

postgres=# EXPLAIN ANALYZE SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text ~ '5') AS x;
                                                              QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Aggregate  (cost=20.00..20.01 rows=1 width=0) (actual time=15118.061..15118.061 rows=1 loops=1)
   ->  Function Scan on generate_series x  (cost=0.00..17.50 rows=1000 width=0) (actual time=1724.591..12516.996 rows=5217031 loops=1)
         Filter: ((val)::text ~ '5'::text)
         Rows Removed by Filter: 4782969
 Total runtime: 15147.950 ms
(5 rows)

型

是的，它代表POSIX regex。另一种方法是使用SQL标准方法来处理带有“SIMILAR TO”运算符的正则表达式，尽管它提供的功能集更有限，但可能更容易理解。我认为这是DBA Exchange的一个很好的参考：https://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql