17 update links
source | link

Read the basics about pattern matching in the manualpattern matching in the manual.

Makes this kind of query faster by orders of magnitude. Special considerations apply for locale-specific sort order. Read more about operator classes in the manualoperator classes in the manual. If you are using the standard "C" locale (most people don't), a plain index (with default operator class) will do.

  • LIKELIKE (~~) is simple and fast but limited in its capabilities.
    ILIKEILIKE (~~*) the case insensitive variant.
    pg_trgm extends index support for both.

  • ~~ (regular expression match) is powerful but more complex and may be slow for anything more than basic expressions.

  • SIMILAR TOSIMILAR TO is just pointless. A peculiar halfbreed of LIKE and regular expressions. I never use it. Explanation below.

  • %% is the "similarity" operator, provided by the additional module pg_trgm.

  • @@@@ is the text search operator. See below.

Beginning with PostgreSQL 9.1 you can facilitate the extension pg_trgmpg_trgm to provide index support for any LIKE / ILIKE pattern (and simple regexp patterns with ~) using a GIN or GiST index.

Consider the introduction in the manualintroduction in the manual and the overview of operators and functionsoverview of operators and functions.

The additional module fuzzystrmatchfuzzystrmatch offers some more options, but performance is generally inferior to all of the above.

Read the basics about pattern matching in the manual.

Makes this kind of query faster by orders of magnitude. Special considerations apply for locale-specific sort order. Read more about operator classes in the manual. If you are using the standard "C" locale (most people don't), a plain index (with default operator class) will do.

  • LIKE (~~) is simple and fast but limited in its capabilities.
    ILIKE (~~*) the case insensitive variant.
    pg_trgm extends index support for both.

  • ~ (regular expression match) is powerful but more complex and may be slow for anything more than basic expressions.

  • SIMILAR TO is just pointless. A peculiar halfbreed of LIKE and regular expressions. I never use it. Explanation below.

  • % is the "similarity" operator, provided by the additional module pg_trgm.

  • @@ is the text search operator. See below.

Beginning with PostgreSQL 9.1 you can facilitate the extension pg_trgm to provide index support for any LIKE / ILIKE pattern (and simple regexp patterns with ~) using a GIN or GiST index.

Consider the introduction in the manual and the overview of operators and functions.

The additional module fuzzystrmatch offers some more options, but performance is generally inferior to all of the above.

Read the basics about pattern matching in the manual.

Makes this kind of query faster by orders of magnitude. Special considerations apply for locale-specific sort order. Read more about operator classes in the manual. If you are using the standard "C" locale (most people don't), a plain index (with default operator class) will do.

  • LIKE (~~) is simple and fast but limited in its capabilities.
    ILIKE (~~*) the case insensitive variant.
    pg_trgm extends index support for both.

  • ~ (regular expression match) is powerful but more complex and may be slow for anything more than basic expressions.

  • SIMILAR TO is just pointless. A peculiar halfbreed of LIKE and regular expressions. I never use it. Explanation below.

  • % is the "similarity" operator, provided by the additional module pg_trgm.

  • @@ is the text search operator. See below.

Beginning with PostgreSQL 9.1 you can facilitate the extension pg_trgm to provide index support for any LIKE / ILIKE pattern (and simple regexp patterns with ~) using a GIN or GiST index.

Consider the introduction in the manual and the overview of operators and functions.

The additional module fuzzystrmatch offers some more options, but performance is generally inferior to all of the above.

16 add phrase search
source | link

 

Prefix matchingPrefix matching is also supported:

As well as phrase search since Postgres 9.6:


Why are regular expressions (~) always faster than SIMILAR TO?

Why are regular expressions (~) always faster than SIMILAR TO?


 

Prefix matching is also supported:


Why are regular expressions (~) always faster than SIMILAR TO?

Prefix matching is also supported:

As well as phrase search since Postgres 9.6:

Why are regular expressions (~) always faster than SIMILAR TO?

15 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link
14 add: prefix matching for text search, trigram index support for regexp, levenshtein(), minor edits
source | link
13 use standard operators in 1st query
source | link
12 add ILIKE to list, improve format
source | link
11 make this more complete, since it has a lot of references. Add links, trim noise, improve format.
source | link
10 add info about pg_trgm; deleted 3 characters in body
source | link
9 fix typos, cleanup
source | link
8 complete rewrite with more information
source | link
7 cleanup
source | link
6 more info
source | link
5 more info
source | link
4 add regexp
source | link
3 more info
source | link
2 more info
source | link
1
source | link