在 Postgres，LIKE 和 ~ 的区别

The ~~ operator is equivalent to LIKE. ~, on the other hand, will match using a POSIX regular expression.

~ is the regular expression operator, and has the capabilities implied by that. You can specify a full range of regular expression wildcards and quantifiers; see the documentation for details. It is certainly more powerful than LIKE, and should be used when that power is needed, but they serve different purposes.

Yes, it stands for POSIX regex. Another alternative is to use the SQL standard approach to regular expressions with the "SIMILAR TO" operator,though it provides a more limited set of features,might be easier to understand. I think this is a good reference from dba exchange: https://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql

There is nothing wrong with LIKE and, IMO, no reason to favour ~ over it. Rather the opposite. LIKE is SQL-standard. So is SIMILAR TO, but it isn't widely supported. PostgreSQL's ~ operator (or posix regular expression matching operator) isn't SQL standard.

For that reason, I prefer to use LIKE where it's expressive enough and I only use ~ when I need the power of full regular expressions. If I ever need to port databases it's one less thing that'll hurt. I've tended to use SIMILAR TO when LIKE isn't powerful enough, but after Erwin's comments I think I'll stop doing that and use ~ when LIKE doesn't do the job.

Also, PostgreSQL can use a b-tree index for prefix searches (eg LIKE 'TEST%') with LIKE or SIMILAR TO if the database is in the C locale or the index has text_pattern_ops. Contrary to what I wrote earlier, Pg can also use such an index for a left-anchored posix regex, it just needs an explicit '^TEST.*' so the regex can only match from the beginning. My post earlier incorrectly stated that ~ couldn't use an index for a prefix search. With that difference eliminated it's really down to whether you want to stick to standard compliant features where possible or not.

See this demo SQLFiddle; note the different execution plans. Note the difference between ~ '1234.*' and ~ '^1234.*'.

Given sample data:

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

note that ~ uses a seqscan even when it's substantially more expensive (artifically so due to enable_seqscan) because it has no alternative, while LIKE uses the index. However, a corrected ~ with a left anchor uses the index too:

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)

Overview

LIKE, SIMILAR TO and ~ are the basic pattern matching operators in PostgreSQL.

If you can, use LIKE (~~), it's fastest and simplest.
If you can't, use a regular expression (~), it's more powerful.
Never user SIMILAR TO. It's pointless. See below.

Installing the additional module pg_trgm adds advanced index options and the similarity operator %.
And there is also text search with its own infrastructure and the @@ operator (among others).

Index support is available for each of these operators - to a varying degree. It regularly trumps the performance of other options. But there is a lot of leeway in the details, even with indexes.

Index support

Without pg_trgm, there is only index support for left anchored search patterns. If your database cluster runs with a non-C locale (typical case), you need an index with a special operator class for that, like text_pattern_ops or varchar_pattern_ops. Basic left-anchored regular expressions are supported by this, too. Example:

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

With pg_trgm installed, GIN or GiST indexes are possible with the operator classes gist_trgm_ops or gin_trgm_ops. These indexes support any LIKE expression, not just left anchored. And, quoting the manual:

Beginning in PostgreSQL 9.3, these index types also support index searches for regular-expression matches.

Details:

• PostgreSQL LIKE query performance variations

SIMILAR TO is a very odd construct. PostgreSQL only implements it because it was defined in early versions of the SQL standard. Internally, every SIMILAR TO expression is rewritten with a regular expression. Therefore, for any given SIMILAR TO expression, there is at least one regular expression doing the same job faster. I never use SIMILAR TO.

Further reading:

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I just did a quick and simple benchmark to look at the performance difference between the two operators when no indexes are involved:

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

In this example the LIKE operator is almost twice as fast as the ~ operator. So if speed is of the essence I would lean towards LIKE, though be careful not to optimize prematurely. ~ gives you a lot more flexibility.

For those of you who are interested, here are EXPLAIN plans for the above queries:

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)

Like is just matching a part of the string either at the beginning or End or Middle And tilt (~) is matching with regex

To explain this further let's create a table and insert some values

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

Now let's insert some values in the table

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

Now your table should look like this

 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)

As you can see 'A%' will only get us the values whose name starts with capital 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

As you can see '%a%' will only get us the values whose name has a in between the name.

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


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

As you can see '%a' will only get us the values whose name ends with a.

Case ~ (tilt)

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

As you can see name ~* 't' will only get us the values whose name has t. ~ means case sensitive and ~* means case insensitive so

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

the above query gave us 0 rows as T was not matching with any entries

Now let's consider a case where we only need to fetch the email ids and we don't know what the mail ids have, but we know the pattern of email i.e there will be some letter or number or _ or . or - and then @ and then some more letter or number or - then . then com or in or org etc and we can create the pattern using regular expression.

now let's try to fetch results using regular expression

# 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

Similarly we can fetch some names which has a space in between

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

[a-z]+ means there can be any letter from a to z and + means it might occur 1 or more times and \s means after that there will be a space in between and then again a set of letters which can occur 1 or more times.

Hope this detailed analysis helps.